Merging rows of each category into one row by remo…

Scenario:

Suppose I would like to remove all cells with blank values and then merge rows of each category into one useful row. How will I achieve this requirement?

Sample Data:

Expected Output:

Method1: GroupBy

This method is suitable for few columns.

Method2: Unpivot/Pivot Columns

This method is suitable for many columns.

Select “Category” column à Unpivot other columns.
Filter out rows that are not empty in the “Value” column.
Sort “Attribute” column in ascending order.
Select “Attribute” column à Pivot by “Value” column, choose “Don’t Aggregate” by expanding Advanced options.

Method3: Fill Function

This method is suitable for the data that contains cells or rows with blank (null) values.

For column “A”, we need to replace blank with “null” as shown below:

Use Table.Group() and Table.FillDown() to look for the next blank cell and fill it with the value above.
Expand all columns except the “Category” column.
Almost the same with Step2, just switch Table.FillDown() to Table.FillUp() and the expand columns.
Remove the duplicates.

M Operation in Advanced Editor:

let

    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUQIiYxMzECNWBy4CREYGRob6FvpGBnBx18rUnMo8qAKQoBGGYlNkcXNLTIVKsbEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Category = _t, A = _t, B = _t, C = _t]),

    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Category", Int64.Type}, {"A", type text}, {"B", Int64.Type}, {"C", type date}}),

    #"Replaced Value" = Table.ReplaceValue(#"Changed Type","",null,Replacer.ReplaceValue,{"A"}),

    #"Grouped Rows" = Table.Group(#"Replaced Value", {"Category"}, {{"Count",  each Table.FillDown(_,{"A","B","C"}), type table}}),

    #"Expanded Count" = Table.ExpandTableColumn(#"Grouped Rows", "Count", {"A", "B", "C"}, {"A", "B", "C"}),

    #"Grouped Rows1" = Table.Group(#"Expanded Count", {"Category"}, {{"Count", each Table.FillUp(_,{"A","B","C"}), type table}}),

    #"Expanded Count1" = Table.ExpandTableColumn(#"Grouped Rows1", "Count", {"A", "B", "C"}, {"A", "B", "C"}),

    #"Removed Duplicates" = Table.Distinct(#"Expanded Count1")

in

    #"Removed Duplicates"

Method4: Use DAX to create a new table:

New Table=

SUMMARIZE (

    'Table',

    [Category],

    "A", LASTNONBLANK ( 'Table'[A], TRUE () ),

    "B", LASTNONBLANK ( 'Table'[B], TRUE () ),

    "C", LASTNONBLANK ( 'Table'[C], TRUE () )

)

Output: