Dynamic building of dimension tables after the import of csv files from folder

Power Query
1

Hi All,
I am thinking about two or three dynamic dimension tables in power query. As after importing the fresh csv scenario file, in case of some new technologies or Year may be added or deleted to the existing dimension tables, then will old reference csv file will cope these changes.

Scenario: Currently, I imported the csv files from folder and developed the queries (Dimension Tables and Fact Table based on it.

Difficulty: As I import new scenario with some changes (the dimension tables are not reflecting these changes). I have to manually make new tables and merging them with Fact Table). It might be the issue with my workflow.

Requirement: Is it possible that that after creating new csv file, the same dimensions could be retrieved (referenced) from New Master File?

2

@Tanzeel an example diagram of your model would make it easier to give advice.

But with Year dimensions are you not using a DATE dimension table that will take care of the various dates you want to slice data by in your Fact Table?

3

Yes to me this sounds like you might be over complicating something quite straight forward if you just set up a proper date table

You want to make sure that you fact table contains all your data. Then get the correct relationship with your date table.

If you’re just adding more dates of information to your fact table this should all just flow smoothly in your model

Sam

4

Thanks for prompt response.
1- Date Table is merged with Fact Table. (Figure Power Query)

Power%20Query
Power Query.png1345×585 52.8 KB

2- No Issue in Fact Table as all dimension Tables are being merged in it. (Figure Data Model)
Data%20Model1
Data Model1.png1366×768 91.9 KB
Data%20Model2
Data Model2.png1366×728 62.8 KB

3- The model consists of further groups on supply and demand side energy system. The issue is that all these tables (Sector level and Technologies Levels) are referenced on our Fundamental Table (Figure Fundamental Table)
Fundamental%20Table
Fundamental Table.png288×525 14.9 KB
. If in other importing scenarios any technology changes then I think it is not catching these changes in these already built dimension tables/groups.

5

Okay there’s a bit going on here, and it’s really moving over into consulting territory which we try to avoid in the forum.

Can you be really specific in regards to what the issue is rather than just showing everything. I’ve read this a couple of times now and overall it looks like your model is looking pretty good honestly. So I’m not too sure why there is issues in regards to data updating?

And when you say fundamental table, I really have no idea what this means because I see no other reference to this anywhere?

Thanks
Sam

6

@Tanzeel Importing CSV files into power bi also requires a different technique. Do you split your headers fields and data fields in the CSV import?

Like you say if there is inconsistency in the CSV’s subsequent to initial ETL you will run into trouble.

7

Hi @Tanzeel we’ve noticed that no response has been received from you since Nov 22, 2019. We just want to check if you still need further help on this post? In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved. You may reopen a new thread when the need arise.