Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.
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.
Solved! Go to 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.
Proud to be a Super User!
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.
@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.
Proud to be a Super User!
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?
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
145 | |
79 | |
64 | |
52 | |
47 |
User | Count |
---|---|
217 | |
89 | |
76 | |
67 | |
60 |