Forum Discussion
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?
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")
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"
3 Replies
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"
