Merging Rows - Should I create duplicate tables first?

[ddalton]

Hi,

I've a dataset that I've transformed to get into a reasonably suitable format for my needs so far. The transformations involve several filters, and a pivot column event/activity. Currently, I have data in a visual table that is useful but requires one final step to merge rows with the same ID. 

Before I go ahead with that, should I duplicate the current tables first? Particularly, I'm interested in creating a new table based on the information shown in the visual table. Is that possible? If so, will it still be linked to the original data import i.e., will it be linked to the imported dataset when it's rereshed (like how the applied steps in the Power Query Editor are linked). 

Below is a breakdown of the steps I've carried out so far, just to give some context. 

This is a representation of the data I started with:

Index	Date_Time	ID	Code	Value
22	27/05/2022 00:00	573	A1	250
406	27/05/2022 00:00	229	A1	143
645	27/05/2022 00:00	601	C1	250
15	27/05/2022 00:00	477	A1	250
15	27/05/2022 00:00	477	S1	235
20	27/05/2022 00:00	482	A1	250
20	27/05/2022 00:00	482	C1	265
28	27/05/2022 00:00	488	A1	250
34	27/05/2022 00:00	494	A1	250
28	27/05/2022 00:00	488	S1	135
34	27/05/2022 00:00	494	S1	140
39	27/05/2022 00:00	499	A1	250

I pivoted the Code column with the Value column, and I now have something like this:

Index	Date_Time	ID	A1	C1	S1
22	27/05/2022 00:00	573	250
406	27/05/2022 00:00	229	143
645	27/05/2022 00:00	601		250
15	27/05/2022 00:00	477	250
15	27/05/2022 00:00	477		235
34	27/05/2022 00:00	494	250
34	27/05/2022 00:00	494			140
39	27/05/2022 00:00	499	250
28	27/05/2022 00:00	488	250
28	27/05/2022 00:00	488			135
20	27/05/2022 00:00	482	250
20	27/05/2022 00:00	482		265

The final step is to merge rows with the same Index such that I have this:

Index	Date_Time	ID	A1	C1	S1
22	27/05/2022 00:00	573	250
406	27/05/2022 00:00	229	143
645	27/05/2022 00:00	601		250
15	27/05/2022 00:00	477	250	235
34	27/05/2022 00:00	494	250		140
39	27/05/2022 00:00	499	250
28	27/05/2022 00:00	488	250		135
20	27/05/2022 00:00	482	250	265

[negi007 · Accepted solution]

@ddalton you can simply have this output without any transformation in the data like below

[ddalton]

@negi007  That looks like a useful approach. However, I can't seem to add fields to "Rows" or to "Values". I only seem to have "Columns" available in the Visualization area:

[ddalton]

Ah. Realised you're likely using the Matrix visual instead of the Table. I'll give it a shot. 

[ribisht17]

@ddalton 

You should be able to achive this by grouping INDEX

You can create 3 measures which will throw maximum value for each INDEX,

For Example, 

new A1=CALCULATE(MAX(A1),ALLEXCEPT(TABLE_NAME,INDEX))

new B1=CALCULATE(MAX(B1),ALLEXCEPT(TABLE_NAME,INDEX))

new C1=CALCULATE(MAX(C1),ALLEXCEPT(TABLE_NAME,INDEX))

15	27/05/2022 00:00	477	250
15	27/05/2022 00:00	477		235

Regards,

Ritesh