Power Query - Change Data Source with Updated file

Question

Hi,

I am new to the use of power query so this question might have been already answered somewhere although I couldn't find any clues here...

I used power query with an Excel extraction taken from my company's ERP. I added formatting and conditional columns, then uploaded to the Data Model and run a pivot.

Now, I have 2 problems:

1. the first one is about updating the source I use to do my analisys, meaning that the extraction is done on a daily basis because data change continuously and I don't know how to tell the data model that a new version of the same file needs to be considered? What are the steps to follow to do that?
2. provided that I can do what I am trying to as per point 1, will the formatting and conditional column still be there after the update?

Thanks a lot

Andrea

Answer 1

I used power query with an Excel extraction taken from my company's ERP.

That's a problem, you shouldn't do it that way. Export the data into a CSV file (or a separate Excel file if CSV is not possible).
Name that file e.g. "Data.CSV" so you know that is the current data file.

Create a new Excel file and import the data using Power Query, after that you can do with the data whatever you want.

Tomorrow, rename the current export file to "Data 2022-06-13.CSV" and you have a backup of the data. Export the new data from the ERP to "Data.CSV" (as you did yesterday) open your Excel file and click Data \ Refresh All (may you need to update your Pivot tables), done.

Formatting, sort and filter is preserved by default

I do this every day at my job, no problems.

Andreas.

Answer 2

Hi Andreas, it worked just fine, amazing, thanks a lot. I loved how clearly you explained each single step.

Can I ask you one more thing: let's say the next day extraction has an extra column to it compared to the extraction done the day before. How do you tell Power query that the source changed by that extra column?

Thanks a lot,

Andrea

Answer 3

say the next day extraction has an extra column to it compared to the extraction done the day before. How do you tell Power query that the source changed by that extra column?

Hi Andrea,

Edit the query and go to the Source step. In the formula bar you can see an MCode like this:

= Csv.Document(File.Contents("Z:\test.csv"),[Delimiter=",", Columns=3, Encoding=1252, QuoteStyle=QuoteStyle.None])

My CSV had 3 columns first and if I add a column it is not imported with this MCode.

If we look into the documentation for Csv.Document we see that the Columns argument can be optional, so if we change the MCode to

= Csv.Document(File.Contents("Z:\test.csv"),[Delimiter=",", Encoding=1252, QuoteStyle=QuoteStyle.None])

we get all columns.

Andreas.