Forum Discussion
Pivot Column with Don't aggregate
Hi All,
Can somebody please help me get the expected results as below. I tried using Pivot column with Dont aggregate but i am getting an error for list
Thanks in advance!!
Pivot without aggregation won't combine values for you, as you are expecting for the modules that are completed by Mr/s b.
So you can first use "Group By" to combine those values: select all columns except "Module Name", choose "Group By" with operation Maximum for column Module Name (this is just a dummy operation to have basis code generated) and adjust the code to have the module names combined.
Now you can pivot.
let Source = Table1, #"Grouped Rows" = Table.Group(Source, {"Name", "Email", "Module Status"}, {{"Modules", each Text.Combine([Module name],","), type text}}), #"Pivoted Column" = Table.Pivot(#"Grouped Rows", List.Distinct(#"Grouped Rows"[#"Module Status"]), "Module Status", "Modules") in #"Pivoted Column"
5 Replies
Pivot without aggregation won't combine values for you, as you are expecting for the modules that are completed by Mr/s b.
So you can first use "Group By" to combine those values: select all columns except "Module Name", choose "Group By" with operation Maximum for column Module Name (this is just a dummy operation to have basis code generated) and adjust the code to have the module names combined.
Now you can pivot.
let Source = Table1, #"Grouped Rows" = Table.Group(Source, {"Name", "Email", "Module Status"}, {{"Modules", each Text.Combine([Module name],","), type text}}), #"Pivoted Column" = Table.Pivot(#"Grouped Rows", List.Distinct(#"Grouped Rows"[#"Module Status"]), "Module Status", "Modules") in #"Pivoted Column"
Great!! Thank You Sir, It Worked Perfectly.Thank you! Now, values cominng in comma separated, I would like to disply each row wise Like below
Source Expected Result Now its displaying Col 1 Col2 A 1 A B C A B C B 2 1 2 3 1,5 2,6 3,7 C 3 5 6 7 A 5 B 6 C 7 Is it possible to disply values like above?
Thank you! Now, values cominng in comma separated, I would like to disply each row wise Like below
Source Expected Result Now its displaying Col 1 Col2 A 1 A B C A B C B 2 1 2 3 1,5 2,6 3,7 C 3 5 6 7 A 5 B 6 C 7 Is it possible to disply values like above?
Hi harish_hpe ,
You can try to use the following m-query:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTJUitWJVnICsozALGcgyxjMAsmawGVN4bJmSrGxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Team = _t, Value = _t]), #"Grouped Rows" = Table.Group(Source, {"Team"}, {{"Count", each _, type table [Team=nullable text, Value=nullable text]}}), #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([Count],"index",1)), #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Team", "Count"}), #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Columns", "Custom", {"Team", "Value", "index"}, {"Custom.Team", "Custom.Value", "Custom.index"}), #"Renamed Columns" = Table.RenameColumns(#"Expanded Custom",{{"Custom.Team", "Team"}, {"Custom.Value", "Value"}, {"Custom.index", "index"}}), #"Pivoted Column" = Table.Pivot(#"Renamed Columns", List.Distinct(#"Renamed Columns"[Team]), "Team", "Value") in #"Pivoted Column"If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Best Regards,
Dedmon Dai
