Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hello everyone,
I have a table "Project" with the following columns.
Order number | Order | January | February | ...
The individual months contain the incoming orders for the project.
This table is to be transformed into an "Incoming orders" table with the following columns.
Incoming order ID | Incoming order date | Incoming order value
I am hoping for a better representation of the incoming orders.
Can you help me how I can transform the table?
Solved! Go to Solution.
Ok.
You can replicate the unpivot in DAX, but it is a bit clunky.
Here is an example code...
Order Table Unpivoted =
var _jan =
SELECTCOLUMNS(Project, "Order number", [Order number], "Order", [Order], "Month", "January", "Value", [January])
var _feb =
SELECTCOLUMNS(Project, "Order number", [Order number], "Order", [Order], "Month", "February", "Value", [February])
var _mar =
SELECTCOLUMNS(Project, "Order number", [Order number], "Order", [Order], "Month", "March", "Value", [March])
Return
UNION(_jan, _feb, _mar)
Use this code to create a new table in DAX. Follow the pattern for the remaining months.
Hope this works for you.
Proud to be a Super User! | |
Thank you for the quick and helpful answer, but the command is not displayed for this table. Is it because the order number is based on a distinct command?
I do not understand what you mean when you say the command is not displayed for the table. Could you provide more detail for that statement?
Proud to be a Super User! | |
I receive the information on the individual projects via an API from our ERP system. This table contains the incoming order value as a string. In the "projects" table, the string is converted into individual columns for each month.
I cannot see the table within the Power Query Editor. All other tables are displayed.
Ok.
You can replicate the unpivot in DAX, but it is a bit clunky.
Here is an example code...
Order Table Unpivoted =
var _jan =
SELECTCOLUMNS(Project, "Order number", [Order number], "Order", [Order], "Month", "January", "Value", [January])
var _feb =
SELECTCOLUMNS(Project, "Order number", [Order number], "Order", [Order], "Month", "February", "Value", [February])
var _mar =
SELECTCOLUMNS(Project, "Order number", [Order number], "Order", [Order], "Month", "March", "Value", [March])
Return
UNION(_jan, _feb, _mar)
Use this code to create a new table in DAX. Follow the pattern for the remaining months.
Hope this works for you.
Proud to be a Super User! | |
You may want to explore the Table.UpivotOtherColumns function.
Microsoft Learn - Table.UnpivotOtherColumns
You could use the UI button to access this as well.
Proud to be a Super User! | |