Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply

Merging rows by new field name

I have a table which has a list of properties.  Each of those properties can have one or more report types represented in a separate column, with a row for each report type present (3 different types creates 3 rows of data).  There is one type that is a precursor for others, and as currently constructed, shows two rows of identical data, treating it as separate report types.  I need to create logic which would say that if a property has Type A and Type B report types, merge into one row under a new report type of "Type A - Type B".  This would need to also be done for two others (Type A - Type B; Type A - Type C; Type A - Type D).

 

Current

Property 1Type A100350
Property 1Type B100350


Desired

Property 1Type A - Type B100350


My first attempt was to create a counter for each type.  I then would create an if statement that said if those two were present name this (Type A - Type B), if not, resort to original report type.  The problem with this is that there is a new row for each type, meaning that none would ever satisfy that query. 

 

Property NameReport TypeType A CounterType B Counter
Property 1Type A10
Property 1Tybe B01

 

If I remove the original report type field from the table, the rows consolidate and the type counter will show multiple for each row

 

Property NameType A CounterType B Counter
Property 111

 

This still won't produce the wanted results, as my attempted query still looks at the rows separately.

 

My other idea is to find a way to create a list for each property that holds each report type within, and do an if in statement to create the logic.  I'm not sure if this is possible.  

 

Any help is greatly appreciated.  Thanks everyone!

 

 

 

1 ACCEPTED SOLUTION

The first step would be to sort your data by the report type, so the outcome fields are all the same. 

Second, use group by, and concatenate all of the report type values in the resulting group: 

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCijKL0gtKqlUMFTSUQqpLEhVcAIyDA0MgKSxqYFSrA42NY6oamIB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Property = _t, Type = _t, #"Value 1" = _t, #"Value 2" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Property", type text}, {"Type", type text}, {"Value 1", Int64.Type}, {"Value 2", Int64.Type}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type",{{"Type", Order.Ascending}}),
    #"Grouped Rows" = Table.Group(#"Sorted Rows", {"Property"}, {{"All Rows", each _, type table [Property=nullable text, Type=nullable text, Value 1=nullable number, Value 2=nullable number]}, {"New Type", each Text.Combine([Type], " - "), type nullable text}})
in
    #"Grouped Rows"

 

Oof, that posts ugly. Click the dropdown on your type field. Sort it ascending. 
Right click your Property field, and choose 'Group By'. 
Select advanced, and create one field for 'All Rows' (assuming you want to preserve the rows) 

Create a second one of type 'Sum' on your 'Type' field. - This will return errors, because you can't sum text. 

Go into the generated code of the step, and replace the sum bit with: 

{"New Type", each Text.Combine([Type], " - "), type nullable text}

 

View solution in original post

4 REPLIES 4
YalanWu_test
Helper I
Helper I

Hi, @TakeoffGoatMigo ;

You could add a funtion.

= Table.Group(#"Sorted Rows", {"Property", "Value 1", "Value 2"}, {{"Type", each Text.Combine( #"Sorted Rows"[Type] ,"-") ,type text}})

See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCijKL0gtKqlUMFTSUQqpLEhVcAIyDA0MgKSxqYFSrA42NY6oamIB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Property = _t, Type = _t, #"Value 1" = _t, #"Value 2" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Property", type text}, {"Type", type text}, {"Value 1", Int64.Type}, {"Value 2", Int64.Type}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type",{{"Type", Order.Ascending}}),
    Custom1 = Table.Group(#"Sorted Rows", {"Property", "Value 1", "Value 2"}, {{"Type", each Text.Combine( #"Sorted Rows"[Type] ,"-") ,type text}}),
    #"Reordered Columns" = Table.ReorderColumns(Custom1,{"Property", "Type", "Value 1", "Value 2"})
in
    #"Reordered Columns"

The final show:

YalanWu_test_0-1660030873088.png

Best Regards,

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

MisterFry
Resolver III
Resolver III

Are you wanting to do this in DAX? Or Power Query?

Power Query seems to be the only way to do this given the scale

The first step would be to sort your data by the report type, so the outcome fields are all the same. 

Second, use group by, and concatenate all of the report type values in the resulting group: 

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCijKL0gtKqlUMFTSUQqpLEhVcAIyDA0MgKSxqYFSrA42NY6oamIB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Property = _t, Type = _t, #"Value 1" = _t, #"Value 2" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Property", type text}, {"Type", type text}, {"Value 1", Int64.Type}, {"Value 2", Int64.Type}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type",{{"Type", Order.Ascending}}),
    #"Grouped Rows" = Table.Group(#"Sorted Rows", {"Property"}, {{"All Rows", each _, type table [Property=nullable text, Type=nullable text, Value 1=nullable number, Value 2=nullable number]}, {"New Type", each Text.Combine([Type], " - "), type nullable text}})
in
    #"Grouped Rows"

 

Oof, that posts ugly. Click the dropdown on your type field. Sort it ascending. 
Right click your Property field, and choose 'Group By'. 
Select advanced, and create one field for 'All Rows' (assuming you want to preserve the rows) 

Create a second one of type 'Sum' on your 'Type' field. - This will return errors, because you can't sum text. 

Go into the generated code of the step, and replace the sum bit with: 

{"New Type", each Text.Combine([Type], " - "), type nullable text}

 

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.