Forum Discussion
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.
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
Anonymous
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
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.
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.
