Flattening Rows Containing Values and Text Dynamically

[jgclarke]

I have unpivotted / pivotted my data and created a table that grows dynamically (ie: Each new month added  will result in a new "value" column and a new "Notes" column. The table is shown below:

Acccount |  Value Jan18  |  Value Feb18  |  Notes Jan18  |  Notes Feb18

ABC123  |     100.00       |     null              |   Blah              |  null

ABC123  |         null        |   98.75             |     null             |  blahblah

DEF987  |       222.22      |     null             |    Words          |  null

DEF987  |         null        |     1021.00      |      null              | more words

How do I get a single row for each account instead of multiple lines? The values do not need to be summed as each account will only have 1 value in a colum. The text does not need to be concatenated as there is only one text string per account in a column.

I can achieve the desired result by grouping by account, creating new columns with lists from the grouped table and then extracting values from these lists; but the new column names / field names in my script will not grow dynamically as more data is added.

Can anyone suggest a script that can group the rows dynamically?

My script is below. The table above would be just before the "grouped rows" command in the script. The rows from "grouped rows" down are the lines that I want to make dynamic.

let
    Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Month", type text}, {"Customer", type text}, {"Value", type number}, {"Comment", type text}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Month", "Customer", "Comment"}, "Attribute", "Value.1"),
    #"Inserted Merged Column" = Table.AddColumn(#"Unpivoted Columns", "Merged", each Text.Combine({[Attribute], [Month]}, " - "), type text),
    #"Removed Columns" = Table.RemoveColumns(#"Inserted Merged Column",{"Attribute"}),
    #"Unpivoted Columns1" = Table.UnpivotOtherColumns(#"Removed Columns", {"Month", "Customer", "Value.1", "Merged"}, "Attribute", "Value"),
    #"Inserted Merged Column1" = Table.AddColumn(#"Unpivoted Columns1", "Merged.1", each Text.Combine({[Attribute], [Month]}, " - "), type text),
    #"Pivoted Column" = Table.Pivot(#"Inserted Merged Column1", List.Distinct(#"Inserted Merged Column1"[Merged]), "Merged", "Value.1"),
    #"Pivoted Column1" = Table.Pivot(#"Pivoted Column", List.Distinct(#"Pivoted Column"[Merged.1]), "Merged.1", "Value"),
    #"Removed Columns1" = Table.RemoveColumns(#"Pivoted Column1",{"Attribute", "Month"}),
    #"Grouped Rows" = Table.Group(#"Removed Columns1", {"Customer"}, {{"Table1", each _, type table}}),
    #"Added Custom2" = Table.AddColumn(#"Grouped Rows", "Value Dec17", each Table.Column([Table1],"Value - Dec'17")),
    #"Extracted Values2" = Table.TransformColumns(#"Added Custom2", {"Value Dec17", each Text.Combine(List.Transform(_, Text.From)), type text}),
    #"Added Custom3" = Table.AddColumn(#"Extracted Values2", "Value Jan18", each Table.Column([Table1],"Value - Jan'18")),
    #"Extracted Values3" = Table.TransformColumns(#"Added Custom3", {"Value Jan18", each Text.Combine(List.Transform(_, Text.From)), type text}),
    #"Added Custom" = Table.AddColumn(#"Extracted Values3", "Custom", each Table.Column([Table1],"Comment - Dec'17")),
    #"Extracted Values" = Table.TransformColumns(#"Added Custom", {"Custom", each Text.Combine(List.Transform(_, Text.From)), type text}),
    #"Added Custom1" = Table.AddColumn(#"Extracted Values", "Custom.1", each Table.Column([Table1],"Comment - Jan'18")),
    #"Extracted Values1" = Table.TransformColumns(#"Added Custom1", {"Custom.1", each Text.Combine(List.Transform(_, Text.From)), type text}),
    #"Removed Columns2" = Table.RemoveColumns(#"Extracted Values1",{"Table1"})
in
    #"Removed Columns2"

[MarkS · Accepted solution]

HI @jgclarke,

Based on the information given I think that your raw data looks like this:

If that is the case then the following will get the data in the shape that you want (Change the Source step to refer to your table)

let
    Source = Table
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Month", type text}, {"Customer", type text}, {"Value", type number}, {"Comment", type text}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Month", "Customer"}, "Attribute", "Value.1"),
    #"Added Custom" = Table.AddColumn(#"Unpivoted Other Columns", "Custom", each [Attribute]& "-" & [Month]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Attribute", "Month"}),
    #"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[Custom]), "Custom", "Value.1")
in
    #"Pivoted Column"

[MarkS · Accepted solution]

HI @jgclarke,

Based on the information given I think that your raw data looks like this:

If that is the case then the following will get the data in the shape that you want (Change the Source step to refer to your table)

let
    Source = Table
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Month", type text}, {"Customer", type text}, {"Value", type number}, {"Comment", type text}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Month", "Customer"}, "Attribute", "Value.1"),
    #"Added Custom" = Table.AddColumn(#"Unpivoted Other Columns", "Custom", each [Attribute]& "-" & [Month]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Attribute", "Month"}),
    #"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[Custom]), "Custom", "Value.1")
in
    #"Pivoted Column"

[jgclarke · Accepted solution]

Thank you MarkS

That works 100%. 

I think that I was overcomplicating my code.

[deevaker]

Hi jgclarke,

If the number of columns are not much, you can apply max function on the columns 

like : Value Jan 18 New = max(Value Jan 18)

So, that when you pull it against Account it will show only max values but not null

Thanks

[jgclarke]

Hi deevaker

The problem with using the max comand is that it has to be applied against named columns. My number of columns will continue to grow each month so the solution has to be able to work with a dynamic number of columns.