Forum Discussion
Transposing Data
Hi All,
I have seen other posts regarding transposing rows and columns, but none that match my specific circumstance. The current structure of my data table is as such:
| Column 1 | Column 2 | Column 3 | Column 4 | Column 5 | Column 6 |
| null | RETAILER ID | A111 | A222 | A333 | A444 |
| null | PROD. CODE | 0123 | 1234 | 2345 | 3456 |
| null | DESCRIPTION | ABC | BCD | CDE | DEF |
| BELGIUM | Jan 2023 | 0 | 1 | 8 | 4 |
| BELGIUM | Feb 2023 | 1 | 4 | 1 | 5 |
| FRANCE | Jan 2023 | 2 | 6 | 17 | 20 |
I need to make the transpositions to achieve a format like this:
| Country | Date | Retailer ID | Prod. Code | Description | Value |
| BELGIUM | JAN 2023 | A111 | 0123 | ABC | 0 |
| BELGIUM | FEB 2023 | A111 | 0123 | ABC | 1 |
| FRANCE | JAN 2023 | A111 | 0123 | ABC | 2 |
And so on.
Any help would be greatly appreciated.
I also shortened the steps (optimized, not much)
11 Replies
Anonymous Not 100% that this is what you want but see if the attached PBIX takes care of this.
Anonymous So what would the final result look like fully from the example given?
I guess it depends on if you really ONLY have 3 rows with Column 1 as NULL or it is more than that, but you can do the following.
Create a second query referencing the old query, taking only the part of the table that has Column 1 as null and transposing that.
Then do various transformations in the first query
As a last step, create a new query "append as new query" and append the two tables, make sure here that your column names match (you can adjust them manually)
Maybe this gives you what you need to figure it out.
My point is that the structure of your rows are so inherently different that i would separate them completely, transform it so it will fit together somehow and then as a last step actually append them (put them back together)
Thank you. Yes, those 3 null values are the only ones in thet column. I think this is similar to what Greg_Deckler proposed (which is getting me closer but not completely to the solution).
Further, what I shared is a transformation of an original raw data source in an even more unworkable format.
Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.
I also shortened the steps (optimized, not much)
