Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Be one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now

v-rzhou-msft
Community Support
‎09-27-2021 07:26 PM

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

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