Cross Join takes too long and data loading on desktop goes to GB's

[Harika_K]

Hello Community,

I have a table table named Historical_data I have 76k rows with 5 columns(Employee,Jobcode,Compeetncy,Date,Score)

now I created 3 tables in power query 
Employee Table - Distinct Employee (300 unique employees) from Historical_data
Compeetncy Table - Distinct Competency(26 unique names) from Historical_data
Dates Table  -  date rages from(2022-01-01,2023-10-01) (22 unique values) (Min date to max date)

Now I tried to perform a cross join so 300 *26*22 less than 200k records 

The issue is that when I tried to load data to Power BI Desktop it takes more than 1 hr and 3 GB and still loading 
am I doing something wrong !!

This is the code I used to get unique employee,competency and date tables

let
    Source = #"HistoricalData", 
    DistinctDate = Table.Distinct(Source, {"Date"}),
    DateColumnOnly = Table.SelectColumns(DistinctDate, "Date")
in
    DateColumnOnly

This the code I used to cross join 3 tables

let
    Source = EmployeeTable,
    Custom1 = Table.AddColumn(Source, "Competency", each CompetencyTable),
    #"Expanded Competency" = Table.ExpandTableColumn(Custom1, "Competency", {"Competency"}, {"Competency.1"}),
    Custom2 = Table.AddColumn(#"Expanded Competency", "Date", each DateTable),
    #"Expanded Date" = Table.ExpandTableColumn(Custom2, "Date", {"Date"}, {"Date.1"})
in
    #"Expanded Date"

Please help
@amitchandak @lbendlin @Ritaf1983 @Ahmedx @parry2k 

[parry2k]

@Harika_K looks like you are doing everything correctly but I'm unable to understand why you are doing crossjoin in the power query. What is the goal here?

[lbendlin]

Dates Table  -  date rages from(2022-01-01,2023-10-01) (22 unique values)

Nope. 639 values. Probably many more if that is a DateTime column.

Please provide sample data (with sensitive information removed) that covers your issue or question completely, in a usable format (not as a screenshot). Leave out anything not related to the issue.
If you are unsure how to do that please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...
Please show the expected outcome based on the sample data you provided.

If you want to get answers faster please refer to https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...