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 would like to (hopefully) just add rows to the existing table "Production Data".
In the table below, the first 3 columns represent existing content while the last 3 columns represent how I would like the outcome of my request to look.
My goal is to duplicate any row where the Product Code column is "AB". The duplicate row would then modify the Department Column data to say "Sanding". In essance, there are 2 departments that worked on the same "product code" item and we need to account for that work across both department.
So the formula would need to look for the specified Product Code "AB" and if found add a row matching all row content but change the Department column to "Sanding". If any other Product Code is in the Product Code column, it would just keep the one row.
Something as simple as this perhaps?
IF [Product Code] = "AB", then duplicate row but change [Department], "Sanding"
Product Code | Department | quantity | Product Code | Department | quantity | |
AB | Welding | 10 | AB | Welding | 10 | |
AB | Welding | 20 | AB | Welding | 20 | |
AB | Welding | 30 | AB | Welding | 30 | |
AB | Welding | 40 | AB | Welding | 40 | |
AB | Welding | 50 | AB | Welding | 50 | |
AB | Welding | 60 | AB | Welding | 60 | |
CD | Welding | 100 | AB | Sanding | 10 | |
CD | Welding | 100 | AB | Sanding | 20 | |
CD | Welding | 100 | AB | Sanding | 30 | |
CD | Welding | 100 | AB | Sanding | 40 | |
AB | Sanding | 50 | ||||
AB | Sanding | 60 | ||||
CD | Welding | 100 | ||||
CD | Welding | 100 | ||||
CD | Welding | 100 | ||||
CD | Welding | 100 |
hi @JenWilson ,
Here you go. ( Power Query Editor)
Step 1 : Create a duplicate table with the same data. Right click on exisiting table, click on duplicate.
Step 2 : Filter the data in new table based on Prodcut Code = AB
Step 3 : Replace the value from Welding to Sanding
Step 4 : Click on original table, Go to Home -> Append Queries -> Append Queries as New. Select Both the table and in the end you would have one more table with expected rows.
Table 1 :
Table 2
Thanks!
@alekhved , will these steps ensure any newly added data will be addressed in the same way? There are multiple entries coming in daily for these product codes. Thanks!
@JenWilson Another way to achieve this is,
You can use the New table as a control table and maintain your combinations.
This option doesn't seem feasable, as there are hundreds of product codes that I would need to add to the additional table.
hi @JenWilson
That can be done too. In the Advance Editor of Table (2), Change the source defintion as
Source = #"Table",
(here the Table is the original name of the table that you want to replicate)
Thanks!
Unfortunately, I don't think this solution will work as the source is SQL code and it didn't like the # placement.
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 |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
93 | |
84 | |
66 |