
Active Member

October 21, 2018

Just few days ago I have discovered that great tool for working with data sources in Excel called Excel Power Query.
I am struggling now how to achieve what I need. Hopefully the picture will be clear enough as for what I have and what I need.
I believe it should be possible to achieve this using List.Accumulate function (this page was very helpful: http://radacad.com/list-accumu.....n-power-bi - see the subheading "Accumulate as Concatenate") and then transposing the resulting values by the pivot table function - transpose columns.
Still, I was unable to achieve the needed result because some of the indexes are not unique (highlighted with yellow). I got error on these lines. It worked fine for those lines where the Indexes Merged 2 was unique.
Am I correct in my assumption? Or is there any other way to achieve what I need? I got stucked on this and cannot move on without some hint.
Any help would be greatly appreciated.

Active Member

October 21, 2018

Hi, thank you.
I think it really is the way to go. However, in the meantime I have forgotten how to use the Accumulate function properly. I know that when I tampered with my table I was able almost to get what I needed. Now I cannot remember how I managed to do that.
If you could help me with this, I have attached the example file with data and query.
Sheet 1 is just a reminder what I want to achieve.
Sheet 2 contains the data used in the query.
Sheet 3 is the place where the result of query is saved.
Thanks a lot for your help in advance.


November 8, 2013

Hi Prorok,
You can do everything via user interface functions.
I would go for Grouping, List.Combine, then Pivot the Index 4 column, last 4 steps are the ones I added to your query (file also attached):
let
Zdroj = Excel.CurrentWorkbook(){[Name="Tabuľka1_2"]}[Content],
#"Zmenený typ" = Table.TransformColumnTypes(Zdroj,{{"Index 1", Int64.Type}, {"Index 2", Int64.Type}, {"Index 3", Int64.Type}, {"Index 4", Int64.Type}, {"Index 5", Int64.Type}, {"TextToMerge", type text}}),
#"Pridaný index" = Table.AddIndexColumn(#"Zmenený typ", "Index Added", 1, 1),
#"Stĺpce so zmeneným poradím" = Table.ReorderColumns(#"Pridaný index",{"Index 1", "Index 2", "Index 3", "Index 4", "Index 5", "Index Added", "TextToMerge"}),
#"Pridané vlastné" = Table.AddColumn(#"Stĺpce so zmeneným poradím", "Indexes Merged 1", each Number.ToText([Index 1])&"."&Number.ToText([Index 2])&"."&Number.ToText([Index 3])),
#"Pridané vlastné3" = Table.AddColumn(#"Pridané vlastné", "Indexes Merged 2", each Number.ToText([Index 1])&"."&Number.ToText([Index 2])&"."&Number.ToText([Index 3])&"-"&Number.ToText([Index 4])),
#"Pridané vlastné1" = Table.AddColumn(#"Pridané vlastné3", "Indexes Merged 3", each Number.ToText([Index 1])&"."&Number.ToText([Index 2])&"."&Number.ToText([Index 3])&"-"&Number.ToText([Index 4])&"."&Number.ToText([Index Added])),
#"Stĺpce so zmeneným poradím1" = Table.ReorderColumns(#"Pridané vlastné1",{"Index 1", "Index 2", "Index 3", "Index 4", "Index 5", "Index Added", "Indexes Merged 1", "Indexes Merged 2", "Indexes Merged 3", "TextToMerge"}),
#"Zoradené riadky" = Table.Sort(#"Stĺpce so zmeneným poradím1",{{"Index 1", Order.Ascending}, {"Index 2", Order.Ascending}, {"Index 3", Order.Ascending}, {"Index 4", Order.Ascending}, {"Index Added", Order.Ascending}}),
#"Pridané vlastné2" = Table.AddColumn(#"Zoradené riadky", "MergedText", each List.Accumulate([TextToMerge],"",(state,current)=>state¤t)),
#"Grouped Rows" = Table.Group(#"Pridané vlastné2", {"Index 1", "Index 2", "Index 3", "Index 4"}, {{"Aggregate", each _, type table}}),
#"Added Custom" = Table.AddColumn(#"Grouped Rows", "Merge", each Text.Combine([Aggregate][TextToMerge],",")),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Aggregate"}),
#"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Removed Columns", {{"Index 4", type text}}, "en-AU"), List.Distinct(Table.TransformColumnTypes(#"Removed Columns", {{"Index 4", type text}}, "en-AU")[#"Index 4"]), "Index 4", "Merge")
in
#"Pivoted Column"

Answers Post
1 Guest(s)
