How to not recreate Pivot Tables and Dax formulas in Excel when the data table is changed

[Anonymous]

Dear all,

Situation: I created a Dashboard in Excel using Pivot Tables and Dax Formulas and I linked it to a folder, which contains SQL csv table files (from my SQL queries) using PowerQuery. 

Problem: My boss wants me to include two new columns into my SQL query which will expand the csv file by two columns. How can I update my existing power query to include these two new columns without having to recreate my exisiting dashboard (pivot tables)?

P.S.: I plan to change the set-up so that PowerQuery links to the SQL Database directly, but I still wait for the log in data for my company's SQL server. However, it would be awesome if the suggested solution would also work when changing the data source from folder to SQL query without having to recreate the dashboard Pivot tables.

Thanks a lot!!!

[v-jingzhang]

Hi @Anonymous 

Have you got the solution? With a combination of both solutions provided earlier by two super users, it could be resolved I think. Is there any update?

Best Regards,
Community Support Team _ Jing

[KNP · Accepted solution]

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.

[Anonymous]

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"

[KNP]

So, 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.

[smpa01]

@Anonymous  

How can I update my existing power query to include these two new columns without having to recreate my exisiting dashboard (pivot tables)? - If you have already written a SQL query which looks like this

select 
      x,y,z
from
    a

add those two columns at the end of the query

select 
      x,y,z,new1,new2
from
    a

 This will make sure the columns get added at the end when you load the data in PQ from SQL and not in between the exisiting columns which will prevent the report/dashboard from breaking. 

[Anonymous]

Thanks a lot! This worked so far, however, I don't see the new columns in my PowerQuery Editor. How can I I include them? They are in the csv file of the folder but not visible in the Power Query Editor Table.