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, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare 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
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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