Comparing periodic reports

[Anonymous]

I download an inventory forecast report in excel every week.  The intention is to see if stock is increasing, decreasing, how much is being held up in safety stocks, etc.  The excels are all identically named except for the data which i also include in the filename (for example: AllSites_AllMaterials_2020-02.25.xlsx).

The data has a format similar to the table below.  Each table is ~500K lines long.

Material	Plant	Month	Available stock (qty)	Available stock (val)	Safety Stock (qty)	Safety Stock (val)	Total stock (qty)	Total stock (val)
Mat A	site 1	jan.20	5	50	5	50	10	100
Mat A	site 1	feb.20	10	100	5	50	15	150
Mat A	site 1	mar.20	1	10	5	50	6	60
Mat B	site 1	jan.20	2	10,72	0	0	2	10,72
Mat B	site 1	feb.20	2	10,72	0	0	2	10,72
Mat B	site 1	mar.20	2	10,72	0	0	2	10,72
Mat C	site 1	jan.20	4	36	1	9	5	45
Mat C	site 1	feb.20	6	54	1	9	7	63
Mat C	site 1	mar.20	8	72	1	9	9	81

I would typically use a Pivot Chart to display the data:

I want to be able to historically compare the reports to see how actions we take in the business are changing the inventory forecasts, and generate trends from the data.  So at the moment i have 2 dimensional data of value x month, but would like to add a 3rd dimension which shows how that forecast/data changed over time.

How do i load the periodic excel data and get power BI to compare the "snapshots"? 

Is there a way that Power BI automatically sees that all imported data files are in the same format/structure, links the data together, and allows me to show the data over a time series?  Or do i have to do some sorcery to append all the data sets together in 1 sheet with a new column that somehow represents the filename/date the data was taken?

[amitchandak · Accepted solution]

These one should help you how to do

https://insightsoftware.com/blog/power-bi-load-data-from-folder/

https://exceleratorbi.com.au/combine-excel-workbooks-power-query-method-1/

[dax · Accepted solution]

Hi @Anonymous , 

If you want to combine two excel into the same, I think you need to make sure two excels' data strcture and data type are the same, then when you combine, it shoud work. For combine, you could refer to above links.

Or if the excel files are different(like fact table, date table), you could import excel files in it, then create relationship between them based on key column and create measure.

Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Ashish_Mathur · Accepted solution]

Hi,

Additionally, you should also convert the text date into a proper date.  Then build a Calendar Table and create a relationship from the Date column of the Data Table to the Date column of the Calendar Table.  To your visual, you should drag the Date column from the Calendar Table.

[dax · Accepted solution]

Hi @Anonymous , 

If you want to combine two excel into the same, I think you need to make sure two excels' data strcture and data type are the same, then when you combine, it shoud work. For combine, you could refer to above links.

Or if the excel files are different(like fact table, date table), you could import excel files in it, then create relationship between them based on key column and create measure.

Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak · Accepted solution]

These one should help you how to do

https://insightsoftware.com/blog/power-bi-load-data-from-folder/

https://exceleratorbi.com.au/combine-excel-workbooks-power-query-method-1/