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.
I'm using Product #30014 for this example. The data set actually has many products in it.
This table says that product 30014 is created using items (parts) 3316, 3317, 3510, 50121, 50123 and SUB-assembly 34941. The first three columns are formed by matching a product list table to a Bill of Materials table.
To expand the SUB-assembly into its items/parts, I joined a second copy of the Bill of Materials table into the model. The last two columns in the example above tell us that SUB-assembly 34941 is made up of parts 2107A and 3494.
I would like to add a column (called MyNewColumn) to this visualization which does the following:
If [Item ID - Level 2] = blank
then MyNewColumn = [Item ID - Level 1]
Else
MyNewColumn = [Item ID - Level 2]
Any suggestions on how to make this work is greatly appreciated.
The result of this formula should be:
Assembly ID MyNewColumn
30014 3316
30014 3317
30014 2107A
30014 3494
30014 3510
30014 50121
30014 50123
Note that Item ID - Level 1 comes from table 'BOM1" and Item ID - Level 2 comes from table "BOM2."
Also, what I showed below is a data viz, not columns in a data table.
Hi @ninsights ,
What do the tables 'BOM1" and " BOM2." look like? Could you please share the data struct of them to me?
Hi @ninsights ,
Kindly share your sample data and excepted result to me if you don't have any Confidential Information. Please upload your files to One Drive for Business and share the link here.
Hi @ninsights ,
Please create the calculated column as below.
MyNewColumn =
IF (
'Table'[Type] = "Sub",
'Table'[Item ID -Level 2],
"" & 'Table'[Item ID - level 1]
)
Pbix as attached.
Hi @ninsights
You can use Unpivot column in Query Editor ( Power Query ) if you would like to see the solution applied to the data sample then please provide the data sample
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 |
---|---|
113 | |
99 | |
75 | |
73 | |
49 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |