Data table split and remerge

Question

Hi Friends,

I have this data table with "Primary" and "Secondary" in individual rows. They share a unique idea in one column but other columns contain different data. My objective is to combine these rows into one column based on their unique id.

My first thought is to do a table split of "primary" and "secondary" and remerge based off the unique id field.

However, I have little to no experience in power query. Looking for any help or guidance. Thank you!

Answer 1

@Dalt

One way:

let  
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyNlHSUQooysxNLKoEsgyNdEyVYnXgMsGpyfl5KVA5Y6iciamZOZqcCYoMwjxTiLglEKCIGxkYIEsgG2UMkooFAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Type = _t, Data = _t]),  
    #"Changed Type" = Table.TransformColumnTypes(Source,  
        {{"ID", Int64.Type}, {"Type", type text}, {"Data", type number}}  
    ),  
    PrimTable = Table.SelectRows(#"Changed Type", each [Type] = "Primary"),  
    SecTable = Table.SelectRows(#"Changed Type", each [Type] = "Secondary"),  
    Joined = Table.NestedJoin(PrimTable,"ID", SecTable,"ID", "MergedSec"),  
    ExpandedMergedSec = Table.ExpandTableColumn(Joined, "MergedSec",{"Type", "Data"},{"Type2", "Data2"})  
in  
    ExpandedMergedSec  

Any question let me know. If problem solved please mark this reply as answer (can help others...). Thanks & nice day...