pivot? sort of

[SteveIOW]

Dear all,

I am loading in a table of data from SQL that has this sort of format... (only more columns)

Date	Value
05/11/2022	a
05/11/2022	b
05/11/2022	c
01/10/2022	x
01/10/2022	y

I need to get it into this format during the load

Date	Value 1	Value 2	Value 3
05/11/2022	a	b	c
01/10/2022	x	y

given that I don't know what the maximum number of 'Values' I am going to get is.

is anyone able to help?

kind regards

Steve

[SteveIOW · Accepted solution]

Hah - I've done it.

I created a column in the SQL using ROWCOUNT and PARTITION. Then when I loaded that table in I could use pivot in powerBI, using the new rowcount column as the column names and choosing not to aggregate the values.

Sorry if I wasted anyone's time and hopefully it will help someone else out.

Best

Steve