Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
Hi
I am frustated because i try a solution to retreive row, filter based by a list
But i take to much time, and i have a very fast computer
Can someone look at the file below to explain how to do better or nor
Thanks
Nicolas
Solved! Go to Solution.
I hope this will help you.
You can specify account numbers here:
This query filters rows and subrows for specified account numbers and adds account number as last column.
In Source step, chnage address to folder where you have your Grand livre standard + analytique CNFC 2023vnt.xlsx file
let
Source = Folder.Files("C:\Users\Address\nicolast29"),
#"!!_FilteredFile_!!" = Table.SelectRows(Source, each ([Name] = "Grand livre standard + analytique CNFC 2023vnt.xlsx")),
Content = #"!!_FilteredFile_!!"{0}[Content],
#"Imported Excel Workbook" = Excel.Workbook(Content),
Sheet = #"Imported Excel Workbook"{[Item="Grand livre standard + analytiq",Kind="Sheet"]}[Data],
#"Added Index" = Table.AddIndexColumn(Sheet, "Index", 0, 1, Int64.Type),
#"Removed Other Columns" = Table.SelectColumns(#"Added Index",{"Column1"}),
#"Changed Type" = Table.TransformColumnTypes(#"Removed Other Columns",{{"Column1", type text}}),
// True if contains number
#"Added Check" = Table.AddColumn(#"Changed Type", "Check", each
[ a = try List.RemoveMatchingItems(Text.Split([Column1], " "), {"", null}) otherwise {},
b = List.ContainsAny(a, {"445800", "445871", "445877"})
][b], type logical),
#"Filtered Rows" = Table.SelectRows(#"Added Check", each [Check] = true),
RowStartEndPositions = List.PositionOfAny(#"Changed Type"[Column1], List.Buffer(#"Filtered Rows"[Column1]), Occurrence.All),
WantedRows = Table.FromList(List.Combine(List.Transform(List.Split(RowStartEndPositions, 2), each {_{0}.._{1}})), Splitter.SplitByNothing(), type table[Position=Int64.Type]),
#"Merged Queries" = Table.NestedJoin(#"Added Index", {"Index"}, WantedRows, {"Position"}, "WantedRows", JoinKind.LeftOuter),
#"Expanded WantedRows" = Table.ExpandTableColumn(#"Merged Queries", "WantedRows", {"Position"}, {"Position"}),
#"Filtered Rows1" = Table.SelectRows(#"Expanded WantedRows", each ([Position] <> null)),
#"Changed Type1" = Table.TransformColumnTypes(#"Filtered Rows1",{{"Column1", type text}}),
#"Trimmed Text" = Table.TransformColumns(#"Changed Type1",{{"Column1", Text.Trim, type text}}),
Ad_AccountNumber = Table.AddColumn(#"Trimmed Text", "Account Number", each if Text.Length([Column1]) >=10 and not Text.StartsWith([Column1], "TOTAL", Comparer.OrdinalIgnoreCase) then Number.From(Splitter.SplitTextByCharacterTransition({"0".."9"}, {" "})([Column1]){0}?) else null, Int64.Type),
#"Filled Down" = Table.FillDown(Ad_AccountNumber,{"Account Number"}),
#"Removed Columns" = Table.RemoveColumns(#"Filled Down",{"Index", "Position"})
in
#"Removed Columns"
Bonjour @nicolast29
Je n'ai pas réussi à charger vos données
Mais voici un exemple simplifié qui devrait correspondre au besoin exprimé.
J'ai une table avec des comptes 1, 2, 3 ou 4 et des commandes A, B, C ou D
Je veux filtrer les commandes qui contiennent au moins une ligne avec 1 ou 2.
Je regroupe par commande, le List.ContainsAny renvoie TRUE s'il y a 1 ou 2 parmi les comptes de la commande.
Je filtre sur ces TRUE et je recombine les tables initiales
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXJUitWJVjKCs4zhLBM4C6TOCa7OCa7OCS7rDJd1hsu6wE0BsmIB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Comptes = _t, Commande = _t]),
#"Lignes groupées" = Table.Group(
Source,
{"Commande"},
{{"Liste_Comptes", each List.ContainsAny([Comptes], {"1", "2"})},
{"Data", each _, type table}}),
#"Lignes filtrées" = Table.SelectRows(#"Lignes groupées", each [Liste_Comptes] = true),
Data = Table.Combine(#"Lignes filtrées"[Data])
in
Data
Stéphane (qui a plaisir à écrire en français car mon anglais n'est pas au niveau !)
Bonjour
Merci je vais voir si je peux appliquer à mon exemple
Mais déjà cela me permet d'améliorer mon M
Bonne fin de journée
yes in the file Grand livre standard + analytique CNFC 2023vnt, the row are thoses in bold format,
it is only after in my account software that the next 0 are added
I don't want only the row with thoses account number, that, it is easy, but the accounting entries, in wich thoses rows are a part, sorry it is accouning meaning, for example a accounting entrie is like this
As you see the for a accounting entrie, you can have multiple row, but the total amount of credit is equal at the total amount crédit, and the order number (numpiece) is the same for eaxh row
And in my case, i want to found all the accounting entries, in iwh there is one of my 3 account number (bold).
That is why at first i search the order number for the 3 account numbers, and after filter all the row
I hope this will help you.
You can specify account numbers here:
This query filters rows and subrows for specified account numbers and adds account number as last column.
In Source step, chnage address to folder where you have your Grand livre standard + analytique CNFC 2023vnt.xlsx file
let
Source = Folder.Files("C:\Users\Address\nicolast29"),
#"!!_FilteredFile_!!" = Table.SelectRows(Source, each ([Name] = "Grand livre standard + analytique CNFC 2023vnt.xlsx")),
Content = #"!!_FilteredFile_!!"{0}[Content],
#"Imported Excel Workbook" = Excel.Workbook(Content),
Sheet = #"Imported Excel Workbook"{[Item="Grand livre standard + analytiq",Kind="Sheet"]}[Data],
#"Added Index" = Table.AddIndexColumn(Sheet, "Index", 0, 1, Int64.Type),
#"Removed Other Columns" = Table.SelectColumns(#"Added Index",{"Column1"}),
#"Changed Type" = Table.TransformColumnTypes(#"Removed Other Columns",{{"Column1", type text}}),
// True if contains number
#"Added Check" = Table.AddColumn(#"Changed Type", "Check", each
[ a = try List.RemoveMatchingItems(Text.Split([Column1], " "), {"", null}) otherwise {},
b = List.ContainsAny(a, {"445800", "445871", "445877"})
][b], type logical),
#"Filtered Rows" = Table.SelectRows(#"Added Check", each [Check] = true),
RowStartEndPositions = List.PositionOfAny(#"Changed Type"[Column1], List.Buffer(#"Filtered Rows"[Column1]), Occurrence.All),
WantedRows = Table.FromList(List.Combine(List.Transform(List.Split(RowStartEndPositions, 2), each {_{0}.._{1}})), Splitter.SplitByNothing(), type table[Position=Int64.Type]),
#"Merged Queries" = Table.NestedJoin(#"Added Index", {"Index"}, WantedRows, {"Position"}, "WantedRows", JoinKind.LeftOuter),
#"Expanded WantedRows" = Table.ExpandTableColumn(#"Merged Queries", "WantedRows", {"Position"}, {"Position"}),
#"Filtered Rows1" = Table.SelectRows(#"Expanded WantedRows", each ([Position] <> null)),
#"Changed Type1" = Table.TransformColumnTypes(#"Filtered Rows1",{{"Column1", type text}}),
#"Trimmed Text" = Table.TransformColumns(#"Changed Type1",{{"Column1", Text.Trim, type text}}),
Ad_AccountNumber = Table.AddColumn(#"Trimmed Text", "Account Number", each if Text.Length([Column1]) >=10 and not Text.StartsWith([Column1], "TOTAL", Comparer.OrdinalIgnoreCase) then Number.From(Splitter.SplitTextByCharacterTransition({"0".."9"}, {" "})([Column1]){0}?) else null, Int64.Type),
#"Filled Down" = Table.FillDown(Ad_AccountNumber,{"Account Number"}),
#"Removed Columns" = Table.RemoveColumns(#"Filled Down",{"Index", "Position"})
in
#"Removed Columns"
Hi
It take me a half day to decrypt and understant,
It is not exactly what i want because i have only the row for the 3 accounts, and not the row link to them.
But for all the works i make it as a solution,
thanks
in accounting each row have a "order number" and a account number
A lot of row are regouping in a accounting entry , total débit = total crédit
I want to retrieve accounting entries, that have a account number in thoses ( 44580000, 44587100, 44587700)
To do that i retrieve first the order number for each rows for thoses account numbers
And after i use the list gererated to find all the lines that make up the accounting entry
Hi, as @lbendlin mentioned, provide sample date in usable format that could be copy/pasted. (if you don't know how to do it - read my note below). You can also upload your date i.e. to google drive and provide here a link with public permissions. Don't forget to send at least a screenshot with expected result based on sample data.
hi, i am not sure to understand, when i create the ticket i link a access to a onedrive perso, see clc link
but may be i was didn't explain it as clear as wanted, sorry
In your files I've found only bold numbers 44580000, 44587100, 44587700. I'm still not sure what do you exactly need. Do you want to filter rows corresponding to such numbers like this one (no. 445800)?
Provide expected output please. Thank you.
Thank you for the sample data. Please explain what you are trying to achieve with it.
hi, sorry for the delay i was busy
At the end i want to retrieve all the row who are connected together with mandatory a row with a account number in this list ( 445800, 445871, 445877)
It is accounting meaning
I arrive to do it with my query, but it take too much time with my method
The key between each group of row is the number of the piece who is in the first column
The little difficulty is that in the excel file it is not the only information in this column 1
so i make some transformatin
extract the number of the account in a new column (retrive row whi start by " " etc...
after that it is more easy because i can filter for example column 3 where the date is store
Because a row mandatory have a date
So retrieve only the row for ( 445800, 445871, 445877) is not enought
it help me to find a list of all the number of piece for thoses row (col1) , after that i can make a sort a merge with the original file to match
but when i try it , it still take too much time
thanks
Check out the September 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
72 | |
65 | |
42 | |
28 | |
21 |