Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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

7 REPLIES 7
MauricioSouza
Frequent Visitor

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 

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!!
Anonymous
Not applicable

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:

https://community.powerbi.com/t5/Power-Query/Combining-multiple-csv-files-from-folder-each-with-different/m-p/1215420

 

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

 

Anonymous
Not applicable

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors