Merging rows by new field name

[TakeoffGoatMigo]

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 1	Type A	100	350
Property 1	Type B	100	350

Desired

Property 1

Type A - Type B

100

350

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 Name	Report Type	Type A Counter	Type B Counter
Property 1	Type A	1	0
Property 1	Tybe B	0	1

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 Name	Type A Counter	Type B Counter
Property 1	1	1

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!

[YalanWu_test]

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:

Best Regards,

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

[MisterFry]

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

[TakeoffGoatMigo]

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

[MisterFry · 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}