Forum Discussion
Combing data from rows into columns
Hello scott_Abaana
in this case you can add a new column to unite both name-column in a record. Then use Table.Pivot that handles with List.Accumulate the different aggregated records.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUfJLzE0F0ZGpxSCeo6+BgaFSrA5C0ghE58PkjFDkjFE1GoMljaCSJigaTVDkTFHkTFHkzFANNVOKjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [KEY = _t, Name = _t, Valid = _t, NameCode = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"KEY", Int64.Type}, {"Name", type text}, {"Valid", type text}}), #"Added Custom1" = Table.AddColumn(#"Changed Type", "Names", each [Name = [Name], NameCode=[NameCode]]), #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Name", "NameCode"}), #"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[Valid]), "Valid", "Names", each List.Accumulate(_, [], (o,r)=> if Record.FieldCount(o)>0 then [Name= o[Name]& ", " & r[Name], NameCode = o[NameCode]& ", " & r[NameCode]] else r )), #"Expanded Yes" = Table.ExpandRecordColumn(#"Pivoted Column", "Yes", {"Name", "NameCode"}, {"Yes.Name", "Yes.NameCode"}), #"Expanded No" = Table.ExpandRecordColumn(#"Expanded Yes", "No", {"Name", "NameCode"}, {"No.Name", "No.NameCode"}) in #"Expanded No"Copy paste this code to the advanced editor in a new blank query to see how the solution works.
If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too
Have fun
Jimmy
Ok, so I played around with different sources of the code and not able to get the table to bring in all the rows. (as above)
My concern also is that my aim is to understand this and then apply the query to preloaded tables/Queries.
I was trying to see if I could get the pivot to work if started with this code:
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"KEY", Int64.Type}, {"Name", type text}, {"Valid ", type text}})
in
#"Changed Type"
The reality is however the table I will be working with, is actually coming from a set off Queries which already have some Merges, Transformations, Look Ups, and calculations applied. So I need to trying and understand this and then apply it to these. Excited that it looks possible however.
Hello scott_Abaana
you have for sure to replace my source staff with yours. My source-step is only to get an understanding of how you can get to the disered target. Here your code extended with my solution
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"KEY", Int64.Type}, {"Name", type text}, {"Valid", type text}}),
#"Pivoted Column" = Table.Pivot(#"Changed Type", List.Distinct(#"Changed Type"[Valid]), "Valid", "Name", each Text.Combine(_, ", "))
in
#"Pivoted Column"Copy paste this code to the advanced editor in a new blank query to see how the solution works.
If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too
Have fun
Jimmy