Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello,
I have a data set with the following structure (columns)
Attribute SalesYear1 SalesYear1 SalesYear1 CostYear1 CostYear2 CostYear3
I would like to transform this into the following structure (columns)
Attribute Year Sales Costs
I think it would be staright forward without the cost column. However, I don't know how to unpiviot into more than two rows.
Thank you in advance for any help
Solved! Go to Solution.
Duplicate the query.
In one, unpivot the Sales columns and remove the cost columns.
In the other, unpivot the Costs and remove the sales columns.
In each query, alter the data so you have Year in a column by itself
'Merge as New' the two queries on Year and Division (Inner Join).
Expand and tidy as appropriate.
Can you provide some sample data please
This is an example: I can unpivot the three sales columns into two columns (year & sales Value). However; iwould like to have three columns (year, sales value & cost value)
Duplicate the query.
In one, unpivot the Sales columns and remove the cost columns.
In the other, unpivot the Costs and remove the sales columns.
In each query, alter the data so you have Year in a column by itself
'Merge as New' the two queries on Year and Division (Inner Join).
Expand and tidy as appropriate.
This worked out! Thank you for your time & effort to look into this! I appreciate your help!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
104 | |
101 | |
79 | |
72 | |
64 |
User | Count |
---|---|
142 | |
108 | |
101 | |
81 | |
74 |