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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
Nata800
Regular Visitor

Unpivot selected columns based on certain conditions

Hi there,

 

I want to unpivot 3 value columns based on the row content given in another column.

 

Logic: If Category = "Total" unpivot columns Old and New Else unpivot column Value

 

Nata800_0-1679656141169.png

 

How to adjust this formula accordingly? 

Nata800_1-1679656196625.png

 

 

Thank you very much!

BR
Nata

1 ACCEPTED SOLUTION
BA_Pete
Super User
Super User

Hi Nata,

 

Do the unpivot as normal, then just add a custom filter.

Paste this into a new blank query to see the steps to take. It's the conditions in the 'filterRows' step that do the work:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("PY0xDsAgCEWv0jC7CIIw9wrdjIN7ky7eP/WbtNPP58GjNbqeOW5KxGq+Qi0QYuqFemp0jnlkAA8DwDyRq3H+OeMeJytzFNtVl+hbEAAuFYJaYr9T+zGM4SrwSCijCy9d7y8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Category = _t, Old = _t, New = _t, Value = _t]),
    chgTypes = Table.TransformColumnTypes(Source,{{"Category", type text}, {"Old", Int64.Type}, {"New", Int64.Type}, {"Value", Int64.Type}}),

    unpivOthCols = Table.UnpivotOtherColumns(chgTypes, {"Category"}, "Attribute", "Value.1"),

    filterRows = Table.SelectRows(unpivOthCols, each  if [Category] = "Total" then [Attribute] <> "Value" else [Attribute] = "Value")

in
    filterRows

 

Example query turns this:

BA_Pete_0-1679669370841.png

 

...into this:

BA_Pete_1-1679669399244.png

 

Pete



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




View solution in original post

2 REPLIES 2
Nata800
Regular Visitor

thank you so much Pete, works perfectly!

BA_Pete
Super User
Super User

Hi Nata,

 

Do the unpivot as normal, then just add a custom filter.

Paste this into a new blank query to see the steps to take. It's the conditions in the 'filterRows' step that do the work:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("PY0xDsAgCEWv0jC7CIIw9wrdjIN7ky7eP/WbtNPP58GjNbqeOW5KxGq+Qi0QYuqFemp0jnlkAA8DwDyRq3H+OeMeJytzFNtVl+hbEAAuFYJaYr9T+zGM4SrwSCijCy9d7y8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Category = _t, Old = _t, New = _t, Value = _t]),
    chgTypes = Table.TransformColumnTypes(Source,{{"Category", type text}, {"Old", Int64.Type}, {"New", Int64.Type}, {"Value", Int64.Type}}),

    unpivOthCols = Table.UnpivotOtherColumns(chgTypes, {"Category"}, "Attribute", "Value.1"),

    filterRows = Table.SelectRows(unpivOthCols, each  if [Category] = "Total" then [Attribute] <> "Value" else [Attribute] = "Value")

in
    filterRows

 

Example query turns this:

BA_Pete_0-1679669370841.png

 

...into this:

BA_Pete_1-1679669399244.png

 

Pete



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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

MayPowerBICarousel

Power BI Monthly Update - May 2024

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

Top Solution Authors
Top Kudoed Authors