DAX : how to convert multiple columns into 2 columns

[fmouhcine]

Dear Community,

Will that be possible, using dax, to convert multiple columns in 1 table to only 2 columns ?

Example:

I do have a table with the monthly product volume per country, where I do have 1 column per product ID as below:

Period	Country	Product_1	Product_2	Product_3	Product_4	Product_5
1/1/2023	France	0	3	5	6	0
1/1/2023	UK	3	6	1	0	9
1/1/2023	South Africa	1	1	3	7	0
2/1/2023	South Africa	0	0	0	0	0
3/1/2023	South Africa	0	0	2	0	0

and what I want to have is: have 1 column for the product ID & 1 column for the volume - as below:

Period	Country	Product	Volume
1/1/2023	France	Product_1	0
1/1/2023	France	Product_2	3
1/1/2023	France	Product_3	5
1/1/2023	France	Product_4	6
1/1/2023	France	Product_5	0
1/1/2023	UK	Product_1	3
1/1/2023	UK	Product_2	6
1/1/2023	UK	Product_3	1
1/1/2023	UK	Product_4	0
1/1/2023	UK	Product_5	9
1/1/2023	South Africa	Product_1	1
1/1/2023	South Africa	Product_2	1
1/1/2023	South Africa	Product_3	3
1/1/2023	South Africa	Product_4	7
1/1/2023	South Africa	Product_5	0
2/1/2023	South Africa	Product_1	1
2/1/2023	South Africa	Product_2	1
2/1/2023	South Africa	Product_3	3
2/1/2023	South Africa	Product_4	7
2/1/2023	South Africa	Product_5	0
3/1/2023	South Africa	Product_1	0
3/1/2023	South Africa	Product_2	0
3/1/2023	South Africa	Product_3	2
3/1/2023	South Africa	Product_4	0
3/1/2023	South Africa	Product_5	0

Thanks in advance for your support

[sevenhills]

You can try like this

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtQ31DcyMDJW0lFyK0rMS04FMgyAGCRgCsRmYH6sDorKUG+oCpCsIVSHJbqq4PzSkgwFx7SizOREqDpDqD5zuKlGONUbYGCQemOC6o0Q6mMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Period = _t, Country = _t, Product_1 = _t, Product_2 = _t, Product_3 = _t, Product_4 = _t, Product_5 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Period", type date}, {"Country", type text}, {"Product_1", Int64.Type}, {"Product_2", Int64.Type}, {"Product_3", Int64.Type}, {"Product_4", Int64.Type}, {"Product_5", Int64.Type}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Period", "Country"}, "Attribute", "Value")
in
    #"Unpivoted Other Columns"

Step: Select "Period", "Country" and click Pivot other columns....

Output:

[amitchandak · Accepted solution]

@fmouhcine , In power query Unpivot columns

Unpivot Data(Power Query): https://youtu.be/2HjkBtxSM0g

Learn Power BI: Power Query Table.Unpivot, Table.UnpivotOtherColumns: https://youtu.be/0FEGEAz9UMw

[fmouhcine]

Hi @amitchandak ,

Many thanks for the tips ! that's exaclty what I was looking for

Learning from you (dear community) day after day ...

Thanks

Mouhcine