cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

New Member

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

Hi,

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.

Target Report Sample:

 Date Patient type Staff Name Target 01/07/2024 Inpatient A 14 01/07/2024 Outpatient B 100 01/07/2024 Inaptient C 5 02/07/2024 Inpatient A 10 02/07/2024 Outpatient B 200 02/07/2024 Inaptient C 20

Finalized Report Sample:

 Sl No. Patient ID Patient Type Finalized Date Finalized by 1 119 Inpatient 01/07/2024 A 4 292 Outpatient 01/07/2024 B 5 292 Inpatient 02/07/2024 C 6 52 Outpatient 02/07/2024 B

created report sample:

 Sl No. Patient ID Patient Type Created Date Bill No. 1 536 OP 01/07/2024 BL2 2 641 IP 01/07/2024 BL4 3 255 OP 02/07/2024 BL5 4 536 IP 02/07/2024 BL6

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

4 REPLIES 4
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
New Member

Hi Mr. Ashish,

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

Thanks.

Super User

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
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

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.