How to update column/s for every file from folder in Powerquery?

[wo_guy]

Hi,

I have powerquery to source files from a folder which comes out like this: This is an example for 2 files in the folder.

Source.Name	Column1	Column2	Column3	Column4	Column5	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	Dock 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	Katies	2020-11-06	08:17:35	45.9	42.9	3.0
10th Nov Stocklands Dock 2 GW Cycle Report.csv	The Colour Room	2020-11-06	08:27:28	50.7	43.0	7.7
10th Nov Stocklands Dock 2 GW Cycle Report.csv	Spare	2020-11-06	08:48:46	51.1	43.4	7.7
10th Nov Stocklands Dock 2 GW Cycle Report.csv		Hours Run / Cycle 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	Dock 8
10th Nov Dock 8 GW Cycle.csv		Type ID	Gentle
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
10th Nov Dock 8 GW Cycle.csv	Cleaner 8	2020-11-04	07:02:43	56.2	41.4	14.8
10th Nov Dock 8 GW Cycle.csv	Rebel	2020-11-04	08:53:51	87.1	41.4	45.7
10th Nov Dock 8 GW Cycle.csv	JB Hi-Fi	2020-11-04	11:47:30	55.6	41.2	14.4
10th Nov Dock 8 GW Cycle.csv	Espresso Warriors	2020-11-04	12:26:37	80.9	41.2	39.7

I want the Location ID to be updated as per the file its coming from i.e. Dock 2 or Dock 8 as in this example and also the TypeID

I want the table to be as:

Source.Name	Column1	Column2	Column3	Column4	Column5	Column6	Column7
10th Nov Stocklands Dock 2 GW Cycle Report.csv	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		Dock 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		Dock 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		Dock 2	General Waste
10th Nov Stocklands Dock 2 GW Cycle Report.csv	Katies	2020-11-06	08:17:35	45.9	42.9	3.0		Dock 2	General Waste
10th Nov Stocklands Dock 2 GW Cycle Report.csv	The Colour Room	2020-11-06	08:27:28	50.7	43.0	7.7		Dock 2	General Waste
10th Nov Stocklands Dock 2 GW Cycle Report.csv	Spare	2020-11-06	08:48:46	51.1	43.4	7.7		Dock 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		Dock 8	Gentle
10th Nov Dock 8 GW Cycle.csv	Cleaner 7	2020-11-04	06:34:28	80.0	79.9	0.1		Dock 8	Gentle
10th Nov Dock 8 GW Cycle.csv	Gentle Dental Care	2020-11-04	06:45:53	87.2	41.4	45.8		Dock 8	Gentle
10th Nov Dock 8 GW Cycle.csv	Cleaner 8	2020-11-04	07:02:43	56.2	41.4	14.8		Dock 8	Gentle
10th Nov Dock 8 GW Cycle.csv	Rebel	2020-11-04	08:53:51	87.1	41.4	45.7		Dock 8	Gentle
10th Nov Dock 8 GW Cycle.csv	JB Hi-Fi	2020-11-04	11:47:30	55.6	41.2	14.4		Dock 8	Gentle
10th Nov Dock 8 GW Cycle.csv	Espresso Warriors	2020-11-04	12:26:37	80.9	41.2	39.7		Dock 8	Gentle

Is there any way to do in Powerquery? If so, how? I would really prefer it to hapen in Powerquery as I want it to be as maintenance free as possible.

Else, is there a way to get it done using DAX? What would be the code?

[PhilipTreacy · Accepted solution]

hi @wo_guy 

Try this code which you can also download in this PBIX file

You'll need everything from the #"Added Custom" step onwards - copy/paste into your query after your files are loaded.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("tZbfbpswFMZfxcp1R2zz33dZQtOuW1MBU6RFuWCZpbDSODK0U9+mz9In6wE7tCFkygiTLHEw5PvxHZuPLBYDgos1uhVPKCrE6j5LNr9yNIEKUTSdo/HzKuMo5FshC2OVPw0uBjCitODoegLVnBdrLtMsQ3eJvFdX9VhedBOf/dlwqdSj78GPXjS/ilVSpGKjZNXdvQjHz1vdiSmHx04yNE/ygveiHUFjQbBSp3iI3SGlTj+Prc6V9Mc7Xl/Ol5/dBeEonoVlo0dxUDbp+lt5mIazKEKLm+mynBuFwa6+DeJ6WhRguTrpBI9l+pCjS8nzNZw5Q0KGFFNc1sy2YUD1+oJc39CVZRpYVaZTzXXkrsuJ33xVoGgttg22y6jDiK+JnkF0RQ01B7Vt0O7wo6ZdZhOGvQpCKKkYe6Zd7wzuDbxTPG8gPUZcZu66axv+oVdF797oscjEo0ShEA8HcAq9Vn6RjQ234bd0XE12g0fbRPIDpAXD0UhSr61pWH0gYVyB2RyFjxs03LsDLhHn+PtayXq17DnpfVTq5Kw+qtCSzF4Hmb0cLrK/BPDxtrTFrd1BqEu4tov9lyhtR6m2oQkc4Fdjtc+tD/scwtNn5D0/d/uc6H2OzLZt3g4bZzwpN47bYDjMtOrX18N1SPnv2YErcC+OHGaBKVPT3Doca0eQXt4/OvIaDJdhyizNsJ1DBrFOZoT8J88a+h4YgIyvPRysCng4dVW+fEZX6afLtIEghFmQ6HotbNtwagatPVgnMoJ8C5+pXMDfJClTIfMmjJafStOtt4DfhJm+NrR8Aw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Source.Name = _t, Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t, Column5 = _t, Column6 = _t, Column7 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Source.Name", type text}, {"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}, {"Column7", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Location ID", each if [Column2] = "Location ID" then [Column3] else null),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Type ID", each if [Column2] = "Type ID" then [Column3] else null),
    #"Filled Down" = Table.FillDown(#"Added Custom1",{"Location ID", "Type ID"}),
    #"Filtered Rows" = Table.SelectRows(#"Filled Down", each ([Column1] <> "")),
    #"Promoted Headers" = Table.PromoteHeaders(#"Filtered Rows", [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"10th Nov Stocklands Dock 2 GW Cycle Report.csv", type text}, {"OPERATOR", type text}, {"DATE", type text}, {"TIME", type text}, {"GROSS [KG]", type text}, {"TARE [KG]", type text}, {"NET [KG]", type text}, {"Total [KG]", type text}, {"Dock 2", type text}, {"General Waste", type text}}),
    #"Filtered Rows1" = Table.SelectRows(#"Changed Type1", each ([OPERATOR] <> "OPERATOR")),
    #"Renamed Columns" = Table.RenameColumns(#"Filtered Rows1",{{"Dock 2", "Location ID"}, {"General Waste", "Type ID"}}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Renamed Columns",{{"DATE", type date}, {"TIME", type time}, {"GROSS [KG]", type number}, {"TARE [KG]", type number}, {"NET [KG]", type number}, {"Total [KG]", type number}})
in
    #"Changed Type2"

which will give you this

Regards

Phil

---

If I answered your question please mark my post as the solution.
If my answer helped solve your problem, give it a kudos by clicking on the Thumbs Up.

[DataInsights]

@wo_guy,

Are you using the Folder Connector to import the data into Power BI? If so, a table named Temp will be created with metadata for each file in the folder. You can extract the data you need from the Name column.