Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
Prasanthh60
Regular Visitor

Creating a table with three different distinct excels that has the only common data is date.

Hi,

 

Thanks in advance

Help me to reach a connection model between 3 distinct excels that has only data as common source.

Basically the dashboard should look like the below and everyday I will be running 3 different report and will place in a folder. PowerBi should capture the datas.

 

Prasanthh60_0-1721495150040.png

 

Target Report Sample:

DatePatient typeStaff NameTarget
01/07/2024InpatientA14
01/07/2024OutpatientB100
01/07/2024InaptientC5
02/07/2024InpatientA10
02/07/2024OutpatientB200
02/07/2024InaptientC20

 

Finalized Report Sample:

 

Sl No.Patient IDPatient TypeFinalized DateFinalized by
1119Inpatient01/07/2024A
4292Outpatient01/07/2024B
5292Inpatient02/07/2024C
652Outpatient02/07/2024B

 

created report sample:

Sl No.Patient IDPatient TypeCreated DateBill No.
1536OP01/07/2024BL2
2641IP01/07/2024BL4
3255OP02/07/2024BL5
4536IP02/07/2024BL6

 

@Uzi2019 You have provided a solution last time for my different query. Please have a look at the above.

4 REPLIES 4
Ashish_Mathur
Super User
Super User

Hi,

Create a Calendar Table with calculated column formulas for Year, Month name and Month number.  Sort the Month name by the Month number.  Create a relationship (Many to One and Single) from the Date column of each table to the Date column of the Calendar Table.  In the third table, rename IP to Inpatient and OP to Outpatient.  Creata table (Patient type) with 2 rows - inpatient and outpatient.  Create a relationship (Many to One and Single) from the Patient type column of all three tables to the new patient type table.  To yuor visual, drag patient type from the new table and Date from the Calendar Table.  Write these measures

T = sum('target report'[target])

F = distinctcounta('Finalized report'[patient id])

Hope this helps.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

 

Hi Mr. Ashish,

 

while creating a connection for dates, I got the below error.

 

Prasanthh60_0-1721833383881.png

Thanks.

 

This is happening because there are duplicate dates in the Calendar Table.  I can help further, if you share the download link of the file.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
tharunkumarRTK
Super User
Super User

@Prasanthh60 

Based on the expected output and provided data sample. I would suggest you to add a new 'Source' column in all three excel files which signifies whether the data is from Target/Finalized/Created report data.

 

Then load these three tables into power bi and append them. then you will be able build a matrix visual. Add date column into rows field, Patient and source into columns and count measure into values field.

 



Need Power BI consultation, hire me on UpWork .


If the post helps please give a thumbs up



If it solves your issue, please accept it as the solution to help the other members find it more quickly.




Tharun



Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Feb2025 NL Carousel

Fabric Community Update - February 2025

Find out what's new and trending in the Fabric community.