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.
Hi, I am looking to create a new column on an existing table but I'm not sure of the best way to proceed. My data is as the first table, I want to add the column 'New list name' to the table as the second table at each change of Transaction ID.
So I need to replace the value '(not set)' with the correct list name dependent on the Transaction ID. Thanks
First table
Transaction ID | Product list name | Product SKU | Quantity |
02-140885 | Broadband Product | p3-fttp30 | 1 |
02-140885 | Broadband Product | p3-250 | 1 |
02-140885 | (not set) | installation25 | 1 |
02-140887 | Broadband | p3-fttp31 | 1 |
02-140887 | Broadband | p3 | 1 |
02-140887 | (not set) | phone | 1 |
Second table
Transaction ID | Product list name | Product SKU | Quantity | New list name |
02-140885 | Broadband Product | p3-fttp30 | 1 | Broadband Product |
02-140885 | Broadband Product | p3-250 | 1 | Broadband Product |
02-140885 | (not set) | installation25 | 1 | Broadband Product |
02-140887 | Broadband | p3-fttp31 | 1 | Broadband |
02-140887 | Broadband | p3 | 1 | Broadband |
02-140887 | (not set) | phone | 1 | Broadband |
Solved! Go to Solution.
If you're actually replacing/editing data in the data set, the best way is to use Power Query. DAX is for analyzing the data afterwards.
In the query editor, you can use replace to find all instances of "not set" and replace them with null values. Then you can order the data as needed and fill down to fix the Product List Name. If you want the second column instead of fixing the original, then copy the column and do the same thing.
Note that the fill down has potential to give you funky results if the first item when ordered by Transaction ID is a blank.
In the query editor replace values of (not set) to "null" and then in the transform tab use fill Down on the desired column.
Please mark this as a solution if it answers your question. 🙂
Does it have to be DAX? Would a simple 'Fill Down' in Power Query editor meet the requirement?
If you're actually replacing/editing data in the data set, the best way is to use Power Query. DAX is for analyzing the data afterwards.
In the query editor, you can use replace to find all instances of "not set" and replace them with null values. Then you can order the data as needed and fill down to fix the Product List Name. If you want the second column instead of fixing the original, then copy the column and do the same thing.
Note that the fill down has potential to give you funky results if the first item when ordered by Transaction ID is a blank.
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 |
---|---|
109 | |
99 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
102 | |
87 | |
64 |