EOM data ready for power BI

[Andy1883]

Hi Team

Our internal customers web-based EOM data is populated monthly.  I can download and save into an excel easy enough.

The question is how am I best to save it and in what format to allow easy connection to Power BI.  The data will be the same but will grow by 3000 lines each month.

Should it be 1 big sheet or is there a better way?  Should I save it as a table or raw data?

[#2]

@Andy1883 

Perhaps you may connect directly to the web to get data. If to save CSV is more preferrable from performance point of view.

[#3]

Hi
Thanks for that.
I changed the file to CSV and started adding additional months data but it is not transferring over when i refresh everything. any ideas?

[#4]

@Andy1883 

When you create the query first time, buy default it put into parameters number of found columns, e.g.

= Csv.Document(File.Contents("C:\file.csv"),[Delimiter=",", Columns=6, Encoding=1252, QuoteStyle=QuoteStyle.None])

Change number of columns on null

= Csv.Document(File.Contents("C:\file.csv"),[Delimiter=",", Columns=null, Encoding=1252, QuoteStyle=QuoteStyle.None])

with next refresh it takes actual non-empty columns.

And do not hardcode column names on next steps, e.g. type change