Summarize 1 particular column across multiple tables

[Yuiitsu]

Dear Everyone

I have a question but I cant find the exact solution to my problem anywhere in this forum.

My data have 4 facts table: Budget, Forecast, Booking, Customer relationship

All these 4 facts table have a common column call "Customer name"

So I want to create a table with unique Customer_Key by summarizing the column "Customer name" from all 4 facts table.

This is so that I can connect them in the Star schema with Many to One relationship.

The 4 files are quite big in nature so I need a method that will not freeze my powerbi desktop when loading.

If there are other better work around please show me how I can do this.

Thank you.

[TomMartens]

Hey @Yuiitsu ,

in addition to the advise of @Jihwan_Kim creating a customer dimension table, you can consider to separate the content from the data as outlined in this article: Separate reports from models in Power BI Desktop - Power BI | Microsoft Learn

As soon as you only work on data visualizations your local machine will not be blocked by a data refresh when the data refresh/load is performed by the Power BI Service.

Regards,

Tom

[Yuiitsu]

@TomMartens Thank you for suggesting an alternative!

I am not familiar with seperating reports from models in PowerBI desktop but it is something I would love to explore and learn. 

Surely I will read and try out this method too.

[Jihwan_Kim · Accepted solution]

Hi, 

I am not sure how your source tables looks like, but I tried to create a sample pbix file like below.

One of the best ways is to ask data foundation team to create a customer dimension table. However, if it is not possible, then please try creating in power query editor.

please check the below picture and the attached pbix file.

The M code looks like below in Advanced Editor

let
    Source = Table.Distinct ( Table.Combine({ Table.SelectColumns(budget,{"customer_name"}) , Table.SelectColumns(forecast,{"customer_name"}), Table.SelectColumns(booking,{"customer_name"}), Table.SelectColumns(relationship,{"customer_name"})}) )
in
    Source

[Yuiitsu]

Thank you @Jihwan_Kim 

I tried your method and it works!!

So far I do not find any loading issue, hope it will not get laggy when the files gets bigger in the future!