Forum Discussion
Combing data from rows into columns
I have A set of thousands of lines of data and trying to build a power Query to speed up the process of prepping it for use. The longest part is combining some row data into two different columns. To date, we have mostly carried this out manually.
I have created a very basic version. In this data sample, there are only 3 different names per each Primary Key. In Practice, we have up to 6 but in theory, we could have many more so it can't be limited by the Number.
What I am trying to do initially is get the Names into two columns (Comma-separated) base on True or Fales set of Data in another column as below: There is a column that tells you how many Names there should be (instances of the Primary Key) So Eg for KEY 1 = 3. (if that helps)
The final outcome is to remove the duplicate rows to leave this
Any help on this would be greatly appreciated.
Many thanks in Advance.
Scott
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
13 Replies
Hello scott_Abaana
you can use Table.Pivot and use the 5th parameter to combine the names
Here a practicable example
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUfJLzE0F0ZGpxUqxOggxIxCdjyJkjKTMCCpmglAGEzLFFDKD6YwFAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [KEY = _t, Name = _t, Valid = _t]), #"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
JimmyWow, so that seems to work in part (the part is probably my fault somewhere)
Thank you so much.
For some reason, the source table does not have all the Keys (KEY 3 was missing)
However, for the table it does import; it does combine really well.
This is the output:
So why have I lost some rows?
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
Jimmy i worked perfectly for the Small table. Just trying to fit it in with my real life Data.
My challenge (I think) is that it assumes that "Valid" and "Name" are the only unique columns.
But in my data sheet I have around 40 columns, some of which 35 are related to the KEY column so are the same for every KEY, but around 5 columns are related to the Names (used earlier to do calculations).
I do only need to Keep the names so trying to figure out what what best to make it work.
I think possible removing these before applying the Pivot.
But this code is much easier to understand than the first code. 🙂
Hello scott_Abaana
then I would really appreciate if you would mark the post as solution.
If you have any question about applying the code to your data, feel free to ask
Jimmy
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUfJLzE0F0ZGpxUqxOggxIxCdjyJkjKTMCCpmglAGEzLFFDLDYoE5pgUWmDotYTpjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [KEY = _t, Name = _t, Valid = _t]), #"Raggruppate righe" = Table.Group(Source, {"KEY", "Valid"}, {{"all", each Text.Combine(_[Name],",")}}), #"Colonna trasformata tramite Pivot" = Table.Pivot(#"Raggruppate righe", List.Distinct(#"Raggruppate righe"[Valid]), "Valid", "all") in #"Colonna trasformata tramite Pivot"
