Grouping same IDs and creating new columns from their respective values

[ddalton]

Hey, folks. Brand new to Power BI but I'll do my best to describe my scenario. 

I have multiple tables that I've imported into Power BI Desktop. I have created the following relationships between the tables:

Table_A          Table_B

  Index     *:1    Index

Here's a representation of the dataset:

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

You'll note that there are multiple records for certain Index and ID values (i.e., Index 28 and ID 488 has a row entry for Code A1 and another row entry for Code S1). 

I want to create a new table that essentially groups the Index and ID columns and creates new columns for each unique Code value and matches their respective Value into a single row, like below:

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

What's the best way to handle this in Power BI Desktop? I've tried playing around with grouping in the GUI but I just can't seem to figure it out. 

[Anonymous]

What was the solution to the null errors? I am trying to do the same thing as above but my Code is a course title.

It is not merging all the Unique IDs but leaving some duplicates with Null Values.

My values are all text, not numerical.

[tackytechtom · Accepted solution]

Hi @ddalton ,

How about pivoting in Power Query:

All you need to do is marking the Code column (left click), choose the transform ribbon, click on pivot column, select Value as Value Column and click ok:

Let me know if this helps or if you get stuck soemwhere 🙂

/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/

[ddalton]

Hi @tackytechtom,

Thanks for suggestion - that's definitely the trick for it. It was something I'd been playing around with but kept getting errors with I've managed to resolve now (null/blank values etc.)