Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet 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
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?
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")
Solved! Go to Solution.
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
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
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?
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
26 | |
12 | |
11 | |
11 | |
8 |