cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ladyhaley
Helper II
Helper II

CSV column format changed, How to adapt??

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: 

ladyhaley_0-1618877299338.png

New file headers 

ladyhaley_1-1618877473753.png

 

Please help me, TIA

6 REPLIES 6
watkinnc
Super User
Super User

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


I’m usually answering from my phone, which means the results are visualized only in my mind. You’ll need to use my answer to know that it works—but it will work!!

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 @ladyhaley ,

 

Check the similar thread below:

https://community.powerbi.com/t5/Power-Query/Combining-multiple-csv-files-from-folder-each-with-diff...

 

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

trebgatte
MVP

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

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Top Solution Authors
Top Kudoed Authors