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

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
smpa01
Super User
Super User

Unpivot Others with null

The following currently eliminates all nulls

 

= Table.UnpivotOtherColumns(
    Table.FromRecords({
        [key = "key1", attribute1 = 1, attribute2 = null, attribute3 = 3],
        [key = "key2", attribute1 = 4, attribute2 = 5, attribute3 = 6]
    }),
    {"key"},
    "column1",
    "column2"
)

 

 I can run a replace prior to this method to replace null to 0.

But I am currently working with a large dataset retrieved through API call and I am not exactly sure what are the other values (other than null) Table.UnpivotOtherColumns is capable of eliminating. I don't want to miss out on any value whatsoever as a result of the method above.
Is there any way to handle this within the UnPivotOtherColumns call itself to instruct PQ to return the value of attributes as is without running multiple replace (per known value UnPivotOtherColumns is capable of eliminating) prior to this call?

@ImkeF @AlexisOlson 

The following also does not help

Table.UnpivotOtherColumns(Table.TransformColumnTypes(Table.FromRecords({
        [key = "key1", attribute1 = 1, attribute2 = null, attribute3 = 3],
        [key = "key2", attribute1 = 4, attribute2 = 5, attribute3 = 6]
    }),{{"attribute1", type text}, {"attribute2", type text}, {"attribute3", type text}}), {"key"}, "Attribute", "Value")

 

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs
1 ACCEPTED SOLUTION
ImkeF
Super User
Super User

Hi @smpa01 ,
yes, no need for an (un)pivot operation here. The following method will keep all values by default:

let
    Custom1 = Table.FromRecords({
        [key = "key1", attribute1 = 1, attribute2 = null, attribute3 = 3],
        [key = "key2", attribute1 = 4, attribute2 = 5, attribute3 = 6]
    }),
    #"Added Custom" = Table.AddColumn(Custom1, "OtherValues", each Record.ToTable(Record.RemoveFields(_, {"key"}))),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"key", "OtherValues"}),
    #"Expanded OtherValues" = Table.ExpandListColumn(#"Removed Other Columns", "OtherValues"),
    #"Expanded OtherValues1" = Table.ExpandRecordColumn(#"Expanded OtherValues", "OtherValues", {"Name", "Value"}, {"Name", "Value"})
in
    #"Expanded OtherValues1"

 

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

View solution in original post

3 REPLIES 3
ImkeF
Super User
Super User

Hi @smpa01 ,
I haven't tested it, but my guess is that this should perform very well, as the operation is completely on row-level.

 

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

ImkeF
Super User
Super User

Hi @smpa01 ,
yes, no need for an (un)pivot operation here. The following method will keep all values by default:

let
    Custom1 = Table.FromRecords({
        [key = "key1", attribute1 = 1, attribute2 = null, attribute3 = 3],
        [key = "key2", attribute1 = 4, attribute2 = 5, attribute3 = 6]
    }),
    #"Added Custom" = Table.AddColumn(Custom1, "OtherValues", each Record.ToTable(Record.RemoveFields(_, {"key"}))),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"key", "OtherValues"}),
    #"Expanded OtherValues" = Table.ExpandListColumn(#"Removed Other Columns", "OtherValues"),
    #"Expanded OtherValues1" = Table.ExpandRecordColumn(#"Expanded OtherValues", "OtherValues", {"Name", "Value"}, {"Name", "Value"})
in
    #"Expanded OtherValues1"

 

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

@ImkeF  this is simply awesome and thanks for this.

One follow-up Q - is there any performance implication with the above for a large dataset?

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

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

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

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