Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi I used combine function in power query to combine multiple CSV from sharepoint folder together. I used the sample file transformation - it was working like a charm untill recently - the company who provide us with this data added two new columns in the CSV and now my report is breaking because query it cant adjust for the new one.
Currently I am manually deleting the columns as the data comes but, I would rather not do this all the time - is there a way to adapt power query to just ignore these new columns or something?
For example old file headers:
New file headers
Please help me, TIA
For whoever's still looking for a solution, after all this time. There is a custom function created to support the csv file import, that relies on a sample query. This sample query holds the number of columns to be imported. It should be named something like "File Transform Sample" (translating from Portuguese), and it's structure should be like:
let
Fonte = Csv.Document(Parâmetro1,[Delimiter=";", Columns=15, Encoding=1252, QuoteStyle=QuoteStyle.None]),
#"Cabeçalhos Promovidos" = Table.PromoteHeaders(Fonte, [PromoteAllScalars=true])
in
#"Cabeçalhos Promovidos"
The trick is changin the "Columns" to reflect the correct number of columns on your new CSV file.
Hope that helps
Looks like you know the column names that you want to include. Therefore, at very end of your import csv step, you can add the column names that you need, like:
Source= (...)[[ColumnName],[AnotherColumn],[YetAnotherColumn]]
This will give you a table of only the columns you ask for.
--Nate
let
Source = SharePoint.Files("https://foodstuffs.sharepoint.com/sites/sc-TransportReporting/", [ApiVersion = 15]),
#"Filtered Rows" = Table.SelectRows(Source, each Text.Contains([Folder Path], "Driver Report/Data/OS")),
#"Filtered Hidden Files1" = Table.SelectRows(#"Filtered Rows", each [Attributes]?[Hidden]? <> true),
#"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File", each #"Transform File"([Content])),
#"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}),
#"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "Transform File"}),
#"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File", Table.ColumnNames(#"Transform File"(#"Sample File"))),
#"Changed Type" = Table.TransformColumnTypes(#"Expanded Table Column1",{{"Source.Name", type text}, {"event_timestamp", type datetime}, {"display_name", type text}, {"rego_plate", type text}, {"cost_center", type text}, {"asset_code", type text}, {"driver_alias", type text}, {"driver", type text}, {"odometer", type number}, {"speed", Int64.Type}, {"spd_limit", Int64.Type}, {"spd_band", Int64.Type}, {"course", Int64.Type}, {"longitude", type number}, {"latitude", type number}, {"location", type text}})
in
#"Changed Type"
Hi I am not too familiar with M language, could you better guide me on where along this auto generated code I should be specifying column names??
Hi @Anonymous ,
Check the similar thread below:
Best Regards,
Kelly
Did I answer your question? Mark my post as a solution!
There's an optional keyword in Power Query M that you can set which enables these changes to be ignored. Here's our blog post on how to use it. CoP TIP: Prevent column changes from breaking your Power BI model - Marquee Insights
Thanks!
--Treb
Unfortunately this doesnt fix my issue.
Columns are still being shifted to the left and therefore will show up empty as data type dont match - which is not what I want.
I want to be able to dynamically remove those new columns.
Ah ok. I think Nate's approach should work, assuming they don't rename columns. This issue is what lead me to find the optional parameter.
You may also want to check out @ImkeF blog, https://www.thebiccountant.com/ as she has a lot of great articles related to import actions like this.
Thanks!
--Treb