Log In
Register
Home
Active Member
October 21, 2018
Offline
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.
See Answer
July 16, 2010
Offline
Hi Prorok,
Try adding an Index Column to your table (Add Column > Index Column) before your List.Accumulate step. This should fix the error that occurs because you have duplicates.
If that doesn't work please provide your Excel file including your query.
Mynda
Active Member
October 21, 2018
Offline
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
Offline
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)
