Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi, I've just started using PowerBI & I'm trying to create a dashboard to display stock history by month.
I currently have 3 different databases in MS Access where the data is stored. The only difference between the 3 is that they have different date ranges in each. So April only has data for 01/04/2022 - 30/04/2022, likewise with May & June. My goal is to be able to filter data within Power BI so that I can see all of the data from across the tables, so from April to June.
I've managed to find a way to get around my issue, although I don't think it's best practice? Currently, I have appended the 3 tables so that all of the data is effectively in one table. Which works, but I don't think this is very future-proof because I'll be adding in data for each month. So I'll have to append each month's worth of data onto the table. (There are around 70,000 rows for each day, to give an idea of the scale)
I think that I might be able to resolve this by using relationships between the tables, but I've got no idea how to structure these. All the material I have read does not seem to cover a scenario where tables have the same data, just for different date ranges.
So my question is:
Is this possible to do with relationships & if so, how would I structure these?
Is this possible to achieve with relationships? If so, where would I start? Ideally, I don't want to have to move away from using MS Access to store each month's data within its own table.
Any help is greatly appreciated.
Follow the steps given in the link
https://docs.microsoft.com/en-us/power-query/append-queries
Did I answer your question? If so, please mark my post as a solution!
Proud to be a Super User!
Why you are not append all these tables with same structure your data will be in a single table
Go to transform and append these tables in PQ
Did I answer your question? If so, please mark my post as a solution!
Proud to be a Super User!
@Depper33 , Here append seem better, as table have same structure and data is adding.
But I would prefer to that source then power query
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
98 | |
95 | |
83 | |
70 | |
66 |
User | Count |
---|---|
118 | |
106 | |
93 | |
79 | |
72 |