Forum Discussion

roscas's avatar
roscas
Helper I
2 years ago
Solved

Show result in only one row

Good afternoon, I need help with the following case. If anyone could help me, I would appreciate it. I have a table where in the 'request' column there are duplicate values because there are values in different columns. What I need is to have only one row with the values from the columns. In the images, you will see what I have and the final result.

 

Result before

 final result

Need Help
  • dufoq3's avatar
    dufoq3
    2 years ago

    Hi roscas,

     

    Before

     

    After

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjOxMLQwt1DSUfLKL05VCM7NLMkAcqAoVgdZBTZFcBUWBkCub2JRZqKCc0ZiWWoVFlPAauAIRTE2NeimYajBYl1sLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Request = _t, #"Policy Holder" = _t, Item1 = _t, #"Item 12" = _t, #"Item 13" = _t]),
    // You can probably delete this step.
    ReplaceBlankToNull = Table.TransformColumns(Source, {}, each if _ = "" then null else _),
    GroupedRows = Table.Group(ReplaceBlankToNull, {"Request"}, {{"All", each 
        [ a = Table.ColumnNames(_),
          b = Table.FillUp(Table.FillDown(_, a), a)
        ][b], type table}}),
    All = Table.Combine(GroupedRows[All])
in
    All

3 Replies

  • dufoq3's avatar
    dufoq3
    Community Champion
    2 years ago

    Hi roscas,

     

    Before

     

    After

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjOxMLQwt1DSUfLKL05VCM7NLMkAcqAoVgdZBTZFcBUWBkCub2JRZqKCc0ZiWWoVFlPAauAIRTE2NeimYajBYl1sLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Request = _t, #"Policy Holder" = _t, Item1 = _t, #"Item 12" = _t, #"Item 13" = _t]),
    // You can probably delete this step.
    ReplaceBlankToNull = Table.TransformColumns(Source, {}, each if _ = "" then null else _),
    GroupedRows = Table.Group(ReplaceBlankToNull, {"Request"}, {{"All", each 
        [ a = Table.ColumnNames(_),
          b = Table.FillUp(Table.FillDown(_, a), a)
        ][b], type table}}),
    All = Table.Combine(GroupedRows[All])
in
    All