Merging table in Power Query taking time

[Mkrishna]

Hello All,

I have a flat file of some 4 million rows and 27 columns(appended various excel files in PBI Online).  I wanted to create a dimension and fact table from this so that I can model them in star schema. I followed the following process.

1. The flat files has store number, store name, state, manager, sales person, brand, products, sales revenue.

To make dimension

1. To make state dimension

1. I reference the above Fact Table. Selected only the State and removed other columns. Removed duplicates. Then added index column starting from 1. Then I merged the State Dimesion with the Fact table on the State value. Then removed the State from Fact table so that it is left with State Index. Now I gave the merged Fact Table name Merged Fact Table

2. To make Products Dimension

1. I referenced the original Fact Table. Selected Brand and Products and removed other columns. Removed the duplicates from Brand and Product(combinely by selecting both brand and columns). The added index columns starting from 1.

2. Merged the above Fact Table called Merged Fact Table (as in 1) with the Product dimension. The common columns are Brand and Products. I expaned the merged table and selected only the Product Index. AFter this removed the Brand and Product column so that the Fact table has only Product Index.

3. To make Staff Dimension

1. Did similar step for Manager and sales person.

There are the process I learned from youtube. People say we need to have index column and all. So followed this path.

The problem now I face is that It take long long long time to load and even a simple query take times. 

1 Is this step of making a index column and merging not necessary. ??

2. Can I directly join State (State1, State2) dimension with the State column of Fact Table instead of making the index column.

3. Similary can I do the same for Brand, Product, Manager, Sales Rep individually i.e. join these dimension with the Fact Table instead of making index column.

Please do help

[parry2k · Accepted solution]

@Mkrishna merging is very expensive and that is why PQ taking a long time. Ideally, you want a number column because the compression of number fields is better than text fields, since it is a small dataset I don't think you need to do this whole dance around index and merge, just use common key columns with fact tables and that will do it. Surely creating the dimension table and star schema is the way to go.

[Mkrishna]

@parry2k Thank you for your response.
So, I do not need to go and make the index column. I can directly link the matching column like
Dimension State(State1, State2) directly with FactTable[State].

Thank you.