Forum Discussion
creating pivot-like table with nulls in repeated rows
Hi, I would like to have a table that would have null value in rows that contains repeating values.
This is what I have:
And this is what I need to get.
Basic idea is that when I have value 1 in IsNewData colum I want to keep the values intact. Otherwise, they should be changed to null.
I was considering using Table.ReplaceValue function. However, it works only on specific column, in this example Column2.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("pY87CoAwEETvktrCxHzPIhZqFDX+7184q5AuKNgMD14mw5Ylq5uWZYyLAum5QDpNzFmVpW3+2/quB0ulkYdWSKtE3E3ZpztOAWysQ56WrJE8dudlpZf34uDMzfLV5h/stp/gQtJiMHSLUDb+nLLoVhc=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t, IsNewData = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", Int64.Type}, {"Column3", type text}, {"Column4", Currency.Type}, {"IsNewData", Int64.Type}}),
#"Replaced Value" = Table.ReplaceValue(#"Changed Type",each [Column2],each if [IsNewData]=1 then [Column2] else null,Replacer.ReplaceValue,{"Column2", "Column3", "Column4"})
in
#"Replaced Value"
In my real data, I need to do this for over 30 columns, so writing this Table.ReplaceValue code for every column would be impractical and prone to error. Does anyone have an idea how I can achieve this? Please note that the columns are of different types.
Hi Anonymous ,
for performance reasons, I would recommend not to replace, but use this method instead:let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("pY87CoAwEETvktrCxHzPIhZqFDX+7184q5AuKNgMD14mw5Ylq5uWZYyLAum5QDpNzFmVpW3+2/quB0ulkYdWSKtE3E3ZpztOAWysQ56WrJE8dudlpZf34uDMzfLV5h/stp/gQtJiMHSLUDb+nLLoVhc=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t, IsNewData = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", Int64.Type}, {"Column3", type text}, {"Column4", Currency.Type}, {"IsNewData", Int64.Type}}), TransformFieldNames = {"Column2", "Column3", "Column4"}, #"Added Custom" = Table.AddColumn(#"Changed Type", "Projection", each if [IsNewData] = 1 then Record.SelectFields(_, TransformFieldNames) else null), #"Removed Columns" = Table.RemoveColumns(#"Added Custom", TransformFieldNames), #"Expanded Projection" = Table.ExpandRecordColumn(#"Removed Columns", "Projection", TransformFieldNames), Custom2 = Value.ReplaceType( Table.ReorderColumns(#"Expanded Projection", Table.ColumnNames(#"Changed Type")), Value.Type(#"Changed Type")) in Custom2
It could be that buffering step "Changed Type" and "TransformFields" would further improve performance.
You define the column names to be transformed once in the list "TransformFieldNames" and can reference them by that name throughout the logic.
3 Replies
Thank you, ImkeF , for your solution!
I have used Select functions for lists and tables, but not for records. You introduced me into very interesting, new area of possibilites. I realized that Replace could be very slow and performance matters in my case. That's another reason why I appreciate your solution even more.
Also, I noticed few times on your blogs you use the Value.ReplaceType quite often (especially in functions), and how you used it here was also thought-provoking to me. Again, this opened new possibilities how to handle changing data types in Power Query which can be painful sometimes.
On a more personal note, I would like to tell you that I have great esteem for your work. I often end up on your blogs looking for ideas on possible solutions. It really makes a difference when you share your experiences, especially when the Microsoft documentation is sometimes very basic.
Hi Anonymous ,
for performance reasons, I would recommend not to replace, but use this method instead:let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("pY87CoAwEETvktrCxHzPIhZqFDX+7184q5AuKNgMD14mw5Ylq5uWZYyLAum5QDpNzFmVpW3+2/quB0ulkYdWSKtE3E3ZpztOAWysQ56WrJE8dudlpZf34uDMzfLV5h/stp/gQtJiMHSLUDb+nLLoVhc=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t, IsNewData = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", Int64.Type}, {"Column3", type text}, {"Column4", Currency.Type}, {"IsNewData", Int64.Type}}), TransformFieldNames = {"Column2", "Column3", "Column4"}, #"Added Custom" = Table.AddColumn(#"Changed Type", "Projection", each if [IsNewData] = 1 then Record.SelectFields(_, TransformFieldNames) else null), #"Removed Columns" = Table.RemoveColumns(#"Added Custom", TransformFieldNames), #"Expanded Projection" = Table.ExpandRecordColumn(#"Removed Columns", "Projection", TransformFieldNames), Custom2 = Value.ReplaceType( Table.ReorderColumns(#"Expanded Projection", Table.ColumnNames(#"Changed Type")), Value.Type(#"Changed Type")) in Custom2
It could be that buffering step "Changed Type" and "TransformFields" would further improve performance.
You define the column names to be transformed once in the list "TransformFieldNames" and can reference them by that name throughout the logic.Thank you so much Anonymous for this kind feedback!
Great to hear what you learned from it.