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:

 

1.png

 

Expected Output:

 

2.png

 

Method1: GroupBy 

 

This method is suitable for few columns.

 

3.png

 

Method2: Unpivot/Pivot Columns

 

This method is suitable for many columns.

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

 

4.png

1.png2.png3.png

 

Method3: Fill Function

 

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

 

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

 

1.png

 

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

 

2.png

3.png

4.png

5.png

 

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: 

 

1.png 

 

Please check the attached file for details.  

 

Hope this article helps everyone with similar questions here.  

 

 

Author: Eyelyn Qin

Reviewer: Ula Huang, Kerry Wang

Merging rows of each category into one row by removing all blank contents.pbix



Source link

Be the first to comment

Leave a Reply

Your email address will not be published.


*