Power Bi does not detect newly added columns to connected csv

[CBVC-HB]

I've tried the advanced editor step to raise the number of columns (from this - https://community.fabric.microsoft.com/t5/Desktop/Power-Bi-does-not-detect-newly-added-columns-to-co...) but that line wasn't there, I think it's due to how it's set up:

let
    Source = Folder.Files("C:\Users\H\OneDrive - C\Documents\P Bi\E - Raw Data"),
    #"Filtered Rows2" = Table.SelectRows(Source, each ([Name] <> "E Leads.csv")),
    #"Invoke Custom Function1" = Table.AddColumn(#"Filtered Rows2", "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}, {"reference", ........

There are more steps after this but it breaks at the 'Changed Type' line, which I shortened here, because it's looking for a column 'Source' which has been moved to the right of the csv file. There are 7 new columns in the middle of the csv files. Any idea how to fix this?

[PijushRoy · Accepted solution]

Hi @CBVC-HB 

Please look into the video https://www.youtube.com/watch?v=qdcAoZU8B8Q and use the CSV.Document to use CSV file as Data source, this method will not create any future issue in place of using traditional method.

Create a new data source and use the method.
Use the advance editor, then copy-paste other steps into the table or copy-paste the CSV upload method in your existing table.

Let me know if that works for you.

Please ACCEPT as SOLUTION

[PijushRoy · Accepted solution]

Hi @CBVC-HB 

Please look into the video https://www.youtube.com/watch?v=qdcAoZU8B8Q and use the CSV.Document to use CSV file as Data source, this method will not create any future issue in place of using traditional method.

Create a new data source and use the method.
Use the advance editor, then copy-paste other steps into the table or copy-paste the CSV upload method in your existing table.

Let me know if that works for you.

Please ACCEPT as SOLUTION

[CBVC-HB]

Thank you it worked. If anyone is wondering, it changed the first lines of my query too:

let
    Source = Folder.Files("C:\Users\H\OneDrive - C\Documents\P"),
    #"Added Custom8" = Table.AddColumn(Source, "Custom", each Csv.Document([Content])),
    #"Filtered Rows2" = Table.SelectRows(#"Added Custom8", each ([Name] <> "El.csv")),
    #"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows2",{"Name", "Custom"}),
    #"Renamed Columns10" = Table.RenameColumns(#"Removed Other Columns",{{"Name", "Source.Name"}}),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Renamed Columns10", "Custom", {"Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8", "Column9", "Column10", "Column11", "Column12", "Column13", "Column14", "Column15", "Column16", "Column17", "Column18", "Column19", "Column20", "Column21", "Column22", "Column23", "Column24", "Column25", "Column26", "Column27", "Column28", "Column29", "Column30", "Column31", "Column32", "Column33", "Column34", "Column35", "Column36", "Column37", "Column38", "Column39", "Column40", "Column41", "Column42", "Column43", "Column44", "Column45", "Column46", "Column47", "Column48", "Column49", "Column50", "Column51", "Column52", "Column53", "Column54", "Column55", "Column56", "Column57", "Column58", "Column59", "Column60", "Column61", "Column62", "Column63", "Column64", "Column65", "Column66", "Column67", "Column68", "Column69", "Column70", "Column71", "Column72", "Column73", "Column74", "Column75", "Column76", "Column77", "Column78", "Column79", "Column80", "Column81", "Column82", "Column83", "Column84", "Column85", "Column86", "Column87", "Column88", "Column89", "Column90", "Column91", "Column92", "Column93", "Column94", "Column95", "Column96", "Column97", "Column98", "Column99", "Column100", "Column101", "Column102", "Column103", "Column104", "Column105", "Column106", "Column107", "Column108", "Column109", "Column110", "Column111", "Column112", "Column113", "Column114", "Column115", "Column116", "Column117", "Column118", "Column119"}, {"Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8", "Column9", "Column10", "Column11", "Column12", "Column13", "Column14", "Column15", "Column16", "Column17", "Column18", "Column19", "Column20", "Column21", "Column22", "Column23", "Column24", "Column25", "Column26", "Column27", "Column28", "Column29", "Column30", "Column31", "Column32", "Column33", "Column34", "Column35", "Column36", "Column37", "Column38", "Column39", "Column40", "Column41", "Column42", "Column43", "Column44", "Column45", "Column46", "Column47", "Column48", "Column49", "Column50", "Column51", "Column52", "Column53", "Column54", "Column55", "Column56", "Column57", "Column58", "Column59", "Column60", "Column61", "Column62", "Column63", "Column64", "Column65", "Column66", "Column67", "Column68", "Column69", "Column70", "Column71", "Column72", "Column73", "Column74", "Column75", "Column76", "Column77", "Column78", "Column79", "Column80", "Column81", "Column82", "Column83", "Column84", "Column85", "Column86", "Column87", "Column88", "Column89", "Column90", "Column91", "Column92", "Column93", "Column94", "Column95", "Column96", "Column97", "Column98", "Column99", "Column100", "Column101", "Column102", "Column103", "Column104", "Column105", "Column106", "Column107", "Column108", "Column109", "Column110", "Column111", "Column112", "Column113", "Column114", "Column115", "Column116", "Column117", "Column118", "Column119"}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Expanded Custom", [PromoteAllScalars=true]),
    #"Renamed Columns1" = Table.RenameColumns(#"Promoted Headers",{{"E.csv", "Source.Name"}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns1",{{"Source.Name", type text}, 

[CBVC-HB · Accepted solution]

Actually I fixed it a different way, the reason it wasn't adding the extra columns is because 1 of the query steps is Expanding the table columns, when doing this it asks you which columns you want. Just had to re-do this step and choose the new columns above the original expanded table column query step and then delete the original query step.

[CBVC-HB]

.....Somehow it didn't work. This is quite annoying, it added the columns but all of the data is null?? Why is that happening?

Edit - Looks like it was to do with the Transform File in the Helper Queries it makes when you use folder as a source. It gives a number of columns value there. Increasing that fixed it.

[PijushRoy]

Hi @CBVC-HB 

I request you please open a new ticket concerning this ticket and it will show top of the list.
So community members can look into and get faster solutions.
https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...