frustration or bad compute for a list contain filter

[nicolast29]

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

clc

Nicolas

[slorin]

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 !)

[nicolast29]

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

[nicolast29]

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

[dufoq3 · Accepted 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"

[nicolast29]

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

[dufoq3]

Which rows do you need then?

[nicolast29]

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

[dufoq3]

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.

[nicolast29]

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

[dufoq3]

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.

[dufoq3]

Ah, sorry, I missed that link in your 1st post. Let me chcek it.

[lbendlin]

Thank you for the sample data. Please explain what you are trying to achieve with it.

[nicolast29]

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