Combing data from rows into columns

[scott_Abaana]

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

[Anonymous]

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"

[Jimmy801]

Hello @scott_Abaana 

Did my solution work?

Jimmy

[scott_Abaana]

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. 🙂

[Jimmy801]

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

[scott_Abaana]

In terms of the thread being helpful for others I will mark the Above solution. It was perfect. 

I got to where I thought I needed to get to, but there was not glitch that my colleague didn't tell me about.

There is a secondary Unique Code that goes with every Name which also needs to be reported. 

Hoping it is as simple as this:

  #"Pivoted Column" = Table.Pivot(#"Changed Type", List.Distinct(#"Changed Type"[Valid]), "Valid", "Name", "Name code", each Text.Combine(_, ", "))

[scott_Abaana]

@Jimmy801 

OK so the slight twist when I went to apply it was that we lost and Unique code that is used to look up the Names

They also need to be put into the YES/NO Column.

I do think I could do it by running two queries of the same table and then merging them, but there is bound to be a cleaner way.

[Jimmy801 · Accepted solution]

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

[scott_Abaana]

I think this works

But to make it work with this source code

Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],

I had to change the expanded date code:   Yes to YES and No to NO, and make sure the data was consistent as I had both. 

But its working on the TEST Data

[Jimmy801]

Hello @scott_Abaana 

it has to be consistent. If you would have Yes and YES you would have to transform it before that is all the time the same. Power Query is case sensitive 🙂

So do you have still some question?

BR

Jimmy

[Jimmy801]

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

Jimmy

[scott_Abaana]

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.

[Jimmy801]

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

[scott_Abaana]

Wow, 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?