How best to add new data into existing dataset from excel

[cathoms]

Hi. Looking for ideas/suggestions about adding new quarterly data to an existing report. I built a report from a single Excel workbook with four distinct sheets. These data are for one calendar year quarter of office visits and I need to add another quarter and will need to add more in future. I'm trying to figure out the best way to add in new data and would appreciate any advice you can provide.

The sheets for both periods (and all periods in future) have the following columns:

NPI

Last Name

First Name

Primary Taxonomy

Group

Total New Office Visits

99202 Freq

99203 Freq

99204 Freq

99205 Freq

99202 Util

99203 Util

99204 Util

99205 Util

99202 Peer

99203 Peer

99204 Peer

99205 Peer

99202 Diff

99203 Diff

99204 Diff

99205 Diff

Notes

99202 CMS Util

99203 CMS Util

99204 CMS Util

99205 CMS Util

99201 CMS Diff

99202 CMS Diff

99203 CMS Diff

99204 CMS Diff

99205 CMS Diff

NPI is National Provider Identifier so each row or 'case' is a provider.  There are also sheets for 99212, 99213, 99214, 99215 Freq, Peer and Diff. The 9920... and 9921... are office visit codes - those with a 0 are new patients and those with a 1 are established patients. For each quarter there are four sheets - established visits > 25, established visits < 25, new visits > 25, new visits < 25.

In power query I took this flat file and created a fact table for all established visits, a fact table for all new visits, and lookup tables for Primary Taxonomy, Group, and Provider, resulting in the following model:

What I need help with or suggestions for is how to incorporate another quarter of data. I'm new enough that I am struggling with how best to model and structure the dataset. How can/should I differentiate the new periods? I don't want to just keep adding new fact tables. I could add additional columns but then my existing query will break. On the other hand, if I need to start from scratch, so be it. 

[v-yalanwu-msft]

Hi, @cathoms ;

You could try to create a new table by dax.

union=var _Q1=summarize('table1',[NPI],[Last Name],[First Name],[Primary Taxonomy]...,"Quarter","Q1")
var  _Q2=summarize('table2',[NPI],[Last Name],[First Name],[Primary Taxonomy]...,"Quarter","Q2")
var _Q3=summarize('table3',[NPI],[Last Name],[First Name],[Primary Taxonomy]...,"Quarter","Q3")
var  _Q4=summarize('table1',[NPI],[Last Name],[First Name],[Primary Taxonomy]...,"Quarter","Q4")
return union(_Q1,_Q2,_Q3,_Q4)

if i understand error  , can you share more infomation about your need and the output you want to show? or a simple after removing the sensetive information.

Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[negi007 · Accepted solution]

@cathoms in your case since you will be adding new data points every quarter, i would suggest to have a new file for each new quarter and then use get data from folder option. You keep adding files for new quarter in the folder and powerbi on refresh will pick data from new files and combine them into one table in powerbi. This way you will not have to keep the same file for all quarter. You will need to ensure that headers in all files are same and powerbi will merge data based on the columns header, column with the same header will be merged into one column.

for more details on this, you can refer to below link. 

https://sqlitybi.com/loading-multiple-excel-files-from-a-folder-in-power-bi/

you can try this method on few excel files to see how it works for you.

[cathoms]

I neglected to mention that I need to set up the report so that end users can view their data by quarter and I need to add in a quarter to quarter comparison. Thus, I don't need to replace the data each quarter I need to add in data each quarter. I think it comes down to how do I differentiate provider data by quarter. Right now my rows are providers. I can't just add in more rows because I would have duplicate NPIs. So how to distinquish Bob Bobson in Q1 from Bob Bobson in Q2?