Merge/Combine rows

[HYirang]

I would like to combine several rows having same Key index like below. ID will be a unique ID of a product.

Table 'weird'

ID	Category	Cate_Value	Size	Color	Price
A	Size	100	100	null	null
A	Color	Blue	null	Blue	null
A	Price	5000	null	null	5000

For your informaiton, the original data set is first 3 columns. and i tried to solve the issue, I added 3 custom columns for each Category.

The result I want to make is like below. What I want is similar with remove duplicate. but all data of column should be reserved.

ID	Category	Cate_Value	Size	Color	Price
A	anything	anything	100	Blue	5000

For your understaing, please see the detail situation and my final goal.

1. first, I know the original data is suck. but I can't change it as it is loaded from online which I can't edit.

2. There are serveral other tables having unique ID like below

Table 'Normal'

ID	Buyer	Producer	Created
A	Olivia	Kevin	10-Jul

3. I can make some relation and report like below using 'Normal' table.

Producer - Color

Created date - Size

Sum of products per producer

sold Qty to Olivia by Kevin

etc...

But I can't get the result between the attributes in table 'weird'.

- Total numbers of speific color per speicifc size.

- Sum of the price for specific size or color

hope you understand this.

ny small hints can help.

[v-easonf-msft]

Hi , @HYirang 

You don't need to  added 3 custom columns for each Category.

Just select the column "Category" and use the function pivot column

Best Regards,
Community Support Team _ Eason
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@HYirang , this needs some data shaping. I think transpose and pivot/unpivot etc

https://radacad.com/pivot-and-unpivot-with-power-bi
Transpose : https://yodalearning.com/tutorials/power-query-helps-transposing-data/

[mahoneypat]

You can use this M query approach to prep your data for a better model.  I put your example "weird" data in, kept the first 3 columns, and then unpivot (w/o aggregation) to get your desired result.  With that table, you can easily make relationships with your other "normal" tables on the ID column and do basic SUM() and COUNT() type measures.

To see the steps, just paste this M code into a blank query, click on Advanced Editor and replace the text there with this:

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUQrOrEoFUoYGBnAyrzQnB0bF6kCUOefn5BcBaaec0lSEEmQeTGVAUWYySNDUAMMwqGBsLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Category = _t, Cate_Value = _t, Size = _t, Color = _t, Price = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", type text}, {"Category", type text}, {"Cate_Value", type text}, {"Size", Int64.Type}, {"Color", type text}, {"Price", Int64.Type}}),
#"Removed Other Columns" = Table.SelectColumns(#"Changed Type",{"ID", "Category", "Cate_Value"}),
#"Pivoted Column" = Table.Pivot(#"Removed Other Columns", List.Distinct(#"Removed Other Columns"[Category]), "Category", "Cate_Value")
in
#"Pivoted Column"

If this works for you, please mark it as solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat