Is there an easy way to sort data per type in power query?

[Will_Smith]

Hi, 

Can I sort data from the table on the left (type A & B mixed in one column)to to the table on the right each type has its own qty column?

[mussaenda]

Hi @Will_Smith ,

[ToddChitt]

Sorry, I missunderstood your original post. I thought the table on the right was what you wanted to GET to. But that is one of the tables you already have? So what are you trying to do here? What would your final data look like?

[Will_Smith]

No, you understood my original post right. I want my final data look like table B, the one on the right. 

My final goal is for table B to table A's connection to the SQL server. I have 2 senarios for solution:

1, Pivot. My understanding is once I do a pivot, my pivot table will be updated as my table A getting refreshed from the SQL server.  If I merge this pivot to table B, would table B gets updated as table A?

2, Sort table A  the same format like table B, then merge table A to table B so table B would have the same connection to SQL server and updates as the database update.

I thought senario 2, might be more straight forward, then I posted my original quetion.

[ToddChitt]

So your data coming out of SQL Server looks like Table A, on thge left. What in your data tell you that the value of 10091 for A should be on the same row as 20024 for B? Is/are there some other column(s) involved?

[Will_Smith]

Yes and yes, there are other columns involved.

[ToddChitt]

I will ask again: What in your data tell you that the value of 10091 for A should be on the same row as 20024 for B? Can you include that in the sample data?

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTI0MLA0BNFKsTrRSk5AlpGBgZEJXASmxhhIG6OoMYOIxAIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Type = _t, QTY = _t, ID = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Type", type text}, {"QTY", Int64.Type}}),
#"Pivoted Column" = Table.Pivot(#"Changed Type", List.Distinct(#"Changed Type"[Type]), "Type", "QTY", List.Sum)
in
#"Pivoted Column"

[Will_Smith]

Hi ToddChitt, 

I got the left hand side table (A)  from SQL server, and planning to merge A to the right hand side table (B), so the data for Type_A_QTY and Type_B_QTY stays up to date.

If I do a Pivot from Table A then merge to Table B, would the data in Table B get update when the data in my SQL server updates?

Thank you!

[ToddChitt]

You should do a PIVOT operation in Power Query. Do you have some other column in there so you don't get a full SUM