Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Will_Smith
Regular Visitor

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

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?
table a.pngtable b.png

8 REPLIES 8
mussaenda
Super User
Super User

Hi @Will_Smith ,

 

mussaenda_0-1693389115125.png

 

ToddChitt
Super User
Super User

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?




Did I answer your question? If so, mark my post as a solution. Also consider helping someone else in the forums!

Proud to be a Super User!





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.

 

 

 

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?




Did I answer your question? If so, mark my post as a solution. Also consider helping someone else in the forums!

Proud to be a Super User!





Yes and yes, there are other columns involved.

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"




Did I answer your question? If so, mark my post as a solution. Also consider helping someone else in the forums!

Proud to be a Super User!





Will_Smith
Regular Visitor

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
Super User
Super User

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




Did I answer your question? If so, mark my post as a solution. Also consider helping someone else in the forums!

Proud to be a Super User!





Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors