Hi
I have powerquery to get files from a folder that comes out like this: This is an example for 2 files in the folder.
Source.Name | Column1 | Column2 | Column3 | Column4 | Column 5 | Column6 | Column7 |
10th Nov Stocklands Dock 2 GW Cycle Report.csv | | Site ID | Wetherill Park | | | | |
10th Nov Stocklands Dock 2 GW Cycle Report.csv | | Owner ID | Suez | | | | |
10th Nov Stocklands Dock 2 GW Cycle Report.csv | | Location ID | Pier 2 | | | | |
10th Nov Stocklands Dock 2 GW Cycle Report.csv | | Type ID | General waste | | | | |
10th Nov Stocklands Dock 2 GW Cycle Report.csv | | Serial ID | 2262-07 / 20 | | | | |
10th Nov Stocklands Dock 2 GW Cycle Report.csv | | Report id | Cycle report | | | | |
10th Nov Stocklands Dock 2 GW Cycle Report.csv | Operator | Date | Time | GROSS [KG] | TARE [KG] | NET [KG] | Total [KG] |
10th Nov Stocklands Dock 2 GW Cycle Report.csv | Trims Fresh | 2020-11-06 | 06:55:55 | 79.5 | 43.0 | 36.5 | |
10th Nov Stocklands Dock 2 GW Cycle Report.csv | The Reject Shop | 2020-11-06 | 07:26:19 | 48.1 | 42.9 | 5.2 | |
10th Nov Stocklands Dock 2 GW Cycle Report.csv | Trims Fresh | 2020-11-06 | 07:51:08 | 121.2 | 43.0 | 78.2 | |
10th Nov Stocklands Dock 2 GW Cycle Report.csv | | Run / cycle hours report | 16 | | | | |
10th Nov Dock 8 GW Cycle.csv | | Site ID | Wetherill Park | | | | |
10th Nov Dock 8 GW Cycle.csv | | Owner ID | Suez | | | | |
10th Nov Dock 8 GW Cycle.csv | | Location ID | Pier 8 | | | | |
10th Nov Dock 8 GW Cycle.csv | | Type ID | Friendly | | | | |
10th Nov Dock 8 GW Cycle.csv | | Serial ID | 2265-07 / 20 | | | | |
10th Nov Dock 8 GW Cycle.csv | | Report id | Cycle report | | | | |
10th Nov Dock 8 GW Cycle.csv | Operator | Date | Time | GROSS [KG] | TARE [KG] | NET [KG] | Total [KG] |
10th Nov Dock 8 GW Cycle.csv | Gentle dental care | 2020-11-04 | 05:59:15 | 79.1 | 41.4 | 37.7 | |
10th Nov Dock 8 GW Cycle.csv | Cleaner 7 | 2020-11-04 | 06:34:28 | 80.0 | 79.9 | 0.1 | |
10th Nov Dock 8 GW Cycle.csv | Gentle dental care | 2020-11-04 | 06:45:53 | 87.2 | 41.4 | 45.8 | |
I want the location ID to be updated in the file it comes from i.e. Dock 2 or Dock 8 as in this example and also the TypeID
I want the table to be like:
Source.Name | Operator | Date | Time | GROSS [KG] | TARE [KG] | NET [KG] | Total [KG] | Location ID | Type ID |
10th Nov Stocklands Dock 2 GW Cycle Report.csv | Trims Fresh | 2020-11-06 | 06:55:55 | 79.5 | 43.0 | 36.5 | | Pier 2 | General waste |
10th Nov Stocklands Dock 2 GW Cycle Report.csv | The Reject Shop | 2020-11-06 | 07:26:19 | 48.1 | 42.9 | 5.2 | | Pier 2 | General waste |
10th Nov Stocklands Dock 2 GW Cycle Report.csv | Trims Fresh | 2020-11-06 | 07:51:08 | 121.2 | 43.0 | 78.2 | | Pier 2 | General waste |
10th Nov Dock 8 GW Cycle.csv | Gentle dental care | 2020-11-04 | 05:59:15 | 79.1 | 41.4 | 37.7 | | Pier 8 | Friendly |
10th Nov Dock 8 GW Cycle.csv | Cleaner 7 | 2020-11-04 | 06:34:28 | 80.0 | 79.9 | 0.1 | | Pier 8 | Friendly |
10th Nov Dock 8 GW Cycle.csv | Gentle dental care | 2020-11-04 | 06:45:53 | 87.2 | 41.4 | 45.8 | | Pier 8 | Friendly |
Is there a way to do it in Powerquery? If so, how? I'd really prefer it to happen in Powerquery as I want it to be as maintenance free as possible.
Otherwise, is there a way to do it using DAX? What would the code be?
