Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
nicolast29
Helper V
Helper V

frustration or bad compute for a list contain filter

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

1 ACCEPTED SOLUTION

I hope this will help you.

You can specify account numbers here:

dufoq3_0-1711794827984.png

 

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"

 

 


Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

View solution in original post

13 REPLIES 13
slorin
Super User
Super User

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

nicolast29
Helper V
Helper V

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

 

nicolast29_0-1711725070684.png

 

I hope this will help you.

You can specify account numbers here:

dufoq3_0-1711794827984.png

 

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"

 

 


Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

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

Which rows do you need then?


Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

nicolast29
Helper V
Helper V

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.


Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide 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)?

dufoq3_0-1711724102701.png

Provide expected output please. Thank you.


Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

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


Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

lbendlin
Super User
Super User

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

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

Top Solution Authors
Top Kudoed Authors