Transposing Data

[Anonymous]

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. 

[Ahmedx]

Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

[Ahmedx · Accepted solution]

I also shortened the steps (optimized, not much)

[Anonymous]

This is fantastic. Thank you!!

[Brunner_BI]

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)

[Anonymous]

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. 

[Greg_Deckler]

@Anonymous Not 100% that this is what you want but see if the attached PBIX takes care of this.

[Anonymous]

Not quite. The next step would be to unpivot Column 3 thru Column 6 in Table 3 of your file. However, it does not produce the desired result as Prod Code 0123 should have monthly sales of 0, 1, and 2. 

[Greg_Deckler]

@Anonymous So what would the final result look like fully from the example given?

[Greg_Deckler]

@Anonymous So what would the final result look like fully from the example given?

[Anonymous]

The issue stems from this: the first 3 rows have a unique structure where the first column is null, the second column is really a column header, and then each remaining column has 1st row Retailer ID, 2nd row Prod Code, 3rd row Product Description with all rows underneath representing the quantity sold for that unique item at the specified country and date in columns 1 and 2. 

[Anonymous]

Something like this (using the PBI sample you provided). 

However the value portion is incorrect based off the sample data. The entries for prod code 123 should look like: