Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

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...
Need Help
  • ImkeF's avatar
    ImkeF
    4 years ago

    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.

     

Anonymous's avatar
Anonymous
Not applicable
4 years ago

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.