split by delimiter - dynamic number of columns

[grengifo]

Hi @ImkeF

I was wondering if you could help me with the following problem.

I created a table that has invoice number in the first column and item number in the second column. Basically it is showing all the items that were purchased in the same invoice.

 

What I need is a file that shows only one row per invoice number and the query should show the item numbers in columns as shown below

The problem is that Power Query limited the number of columns in the split by delimiter step to 3. In the case of invoice 5, it has 5 different products in it (17,18,19, 60 and 61) but the query only shows 17, 18 and 19 and ignored products 60 and 61

I could have manually changed the maximum number of columns to 5 but it is cumbersome to having to do this manually every time new data gets uploaded. I cannot figure out a way to make the split process dynamic so that it adds the necessary number of columns depending on the maximum number of items in the invoices.

Here is the query:

let

Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"invoice", Int64.Type}, {"item", Int64.Type}}),
#"Removed Duplicates" = Table.Distinct(#"Changed Type"),
#"Sorted Rows" = Table.Sort(#"Removed Duplicates",{{"invoice", Order.Ascending}, {"item", Order.Ascending}}),
#"Grouped Rows" = Table.Group(#"Sorted Rows", {"invoice"}, {{"Count", each Table.RowCount(_), type number}, {"AllItems", each _, type table}}),
#"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.Column([AllItems],"item")),
#"Extracted Values" = Table.TransformColumns(#"Added Custom", {"Custom", each Text.Combine(List.Transform(_, Text.From), ";"), type text}),
#"Removed Columns" = Table.RemoveColumns(#"Extracted Values",{"Count"}),
#"Split Column by Delimiter" = Table.SplitColumn(#"Removed Columns", "Custom", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), {"Custom.1", "Custom.2", "Custom.3"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Custom.1", Int64.Type}, {"Custom.2", Int64.Type}, {"Custom.3", Int64.Type}})

in

#"Changed Type1"

Thank you very much for your time and consideration.

[ImkeF · Accepted solution]

You can add a clustered index. Pivot on it then and it will serve as the column names/index.

 Please see this function here: https://github.com/ImkeF/M/blob/master/Library/Table.ClusteredIndex.pq

Let me know if you need help applying this function.

[ImkeF · Accepted solution]

You can add a clustered index. Pivot on it then and it will serve as the column names/index.

 Please see this function here: https://github.com/ImkeF/M/blob/master/Library/Table.ClusteredIndex.pq

Let me know if you need help applying this function.

[grengifo · Accepted solution]

Hi @ImkeF

Thank you very much for your response.

I actually used your solution in the following post and video and I applied them to my particular situation successfully.

Custom-column-Index-or-Ranking-by-other-column/m-p/33883#M12117

https://youtu.be/-3KFZaYImEY

Thank you very much!!