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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors