Forum Discussion
How to not recreate Pivot Tables and Dax formulas in Excel when the data table is changed
I'm not sure I'm clear on the problem. Why would you need to recreate because of new columns?
For your current setup, if you have a line in your Power Query that looks something like...
Source = Csv.Document( File.Contents("yourDirectory\yourFile.csv"), [Delimiter = ",", Columns = 4, Encoding = 1252, QuoteStyle = QuoteStyle.None]remove the part that says "Columns = x," and it will bring through all columns in the file even when new ones are added.
New...
Source = Csv.Document( File.Contents("yourDirectory\yourFile.csv"), [Delimiter = ",", Encoding = 1252, QuoteStyle = QuoteStyle.None]Hope this helps.
As I import from folder, unfortunately, my advanced query editor looks like this:
let
Source = Folder.Files("H:\00_Operations\131_Reporting\Lead Selling Dashboard\Data"),
#"Filtered Hidden Files1" = Table.SelectRows(Source, each [Attributes]?[Hidden]? <> true),
#"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File from Data", each #"Transform File from Data"([Content])),
#"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}),
#"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "Transform File from Data"}),
#"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File from Data", Table.ColumnNames(#"Transform File from Data"(#"Sample File"))),
#"Changed Type" = Table.TransformColumnTypes(#"Expanded Table Column1",{{"Source.Name", type text}, {"id", Int64.Type}, {"create_timestamp", type datetime}, {"ts_deal_signed", type text}, {"ts_closed", type text}, {"update_timestamp", type datetime}, {"property_type", type text}, {"group", type text}, {"Status", type text}, {"Provision", type text}, {"Deal Lender", type text}, {"Gesamtvolumen", Int64.Type}, {"Laufzeit", Int64.Type}, {"Request_Type", type text}, {"Kanton Kunde", type text}, {"Kanton Liegenschaft", type text}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Source.Name"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Removed Columns",{{"Gesamtvolumen", Currency.Type}}),
#"Changed Type with Locale" = Table.TransformColumnTypes(#"Changed Type1", {{"Gesamtvolumen", Currency.Type}}, "de-CH"),
#"Changed Type2" = Table.TransformColumnTypes(#"Changed Type with Locale",{{"Provision", Currency.Type}}),
#"Changed Type with Locale1" = Table.TransformColumnTypes(#"Changed Type2", {{"Provision", Currency.Type}}, "de-CH"),
#"Changed Type3" = Table.TransformColumnTypes(#"Changed Type with Locale1",{{"create_timestamp", type datetime}, {"update_timestamp", type datetime}, {"ts_closed", type datetime}, {"ts_deal_signed", type datetime}}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type3",{{"Status", "Status YTD"}}),
#"Changed Type4" = Table.TransformColumnTypes(#"Renamed Columns",{{"update_timestamp", type date}, {"create_timestamp", type date}})
in
#"Changed Type4"
Super UserSo, in your query pane on the left, you'll have a list of queries. The one you need to change is the "Transform ... Sample File". Your's is probably called something like "Transform File from Data Sample File".
If you click on that and go to the advanced editor, you should see reference to the columns.
let
Source = Csv.Document(Parameter1,[Delimiter=",", Columns=4, Encoding=1252, QuoteStyle=QuoteStyle.None]),
#"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true])
in
#"Promoted Headers"
Changing it there will work for all files that get combined.