Forum Discussion
pivot? sort of
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
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
1 Reply
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