Group by

[Anonymous]

I would like to consolidate the number of Business Value KPI_Metric rows below from 9 to 3 by having the values within the Metric, Target, and Calculation / Approach columns in the same row. For example; row 1 would have Reduce in-center processing, Test LI1, Test LI1 - 10, and Test LI 1. Does anyone know if this can be done in the query editor?

Thanks in advance to anyone who can assist.

[ronrsnfld · Accepted solution]

You can do it within the Table.Group function.

Then just expand the resulting table

• Group by the BV Metric
• Create a new table by combining the columns of the old table as Lists from which you've removed the nulls:  Table.FromColumns
  • You may want to replace any blanks or spaces with nulls before doing the group, depending on your data
• Then filter out the nulls with Table.SelectRows
• In the next step, you can expand this newly created table.

   #"Grouped Rows" = Table.Group(#"Replaced Value", {"Business Value KPI_Metric"}, {
        {"Collapsed", each 
            Table.SelectRows(
                Table.FromColumns(
                    {[Business Value KPI_Metric],List.RemoveNulls([Metric]),List.RemoveNulls([Target]),List.RemoveNulls([#"Calculation/Approach"])},
                    type table [Business Value KPI_Metric=nullable text, 
                                Metric=nullable text, 
                                Target=nullable number, 
                                #"Calculation/Approach"=nullable text]),
            each [Metric]<> null)}}),

Replace #"Previous Step" with the actual name of the previous step in your code.

[Anonymous]

Brilliant. Thank you!