Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

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

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.   ...
Need Help
Show and Tell
Tips and Tricks
  • KNP's avatar
    KNP
    4 years ago

    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.

     

smpa01's avatar
smpa01
Icon for Community Champion rankCommunity Champion
4 years ago

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.