powerBI slow loading data

[cail]

Dear all,

I am using Power BI and have loaded data from Teams via a link, with the data being stored on SharePoint.

I used the following code to calculate 3 new columns, which took approximately 3 hours to generate the values. Afterward, I tried to apply the changes for this table, including the 3 new calculated columns. It has now been 2 days that this table, along with other tables, is still loading. While I can see that the other tables have completed, this one with the calculated columns is still taking 2 days to load.

Could you please advise on how I can optimize the latency of the data loading process?

Thank you in advance for your assistance

= let
FilteredUtilisateurs = Table.SelectRows(Utilisateurs,
each [#"lettre"] = "P"
),

AddCustomColumn = Table.AddColumn(#"Type modifié2", "PARTENAIRE", each
let
codeOrg = [#"Code"],
MatchingRows = Table.SelectRows(FilteredUtilisateurs,
each Text.Contains([#"USERS."], codeOrg)
),
CountRows = Table.RowCount(MatchingRows)
in
if CountRows > 0 then CountRows else "NO"
)
in
AddCustomColumn

[Anonymous · Accepted solution]

Hi @cail ,

Could you please provide sample data or pbix file(does not include sensitive data)? You can also try using Table.Buffer to improve performance.

let

    FilteredUtilisateurs = Table.Buffer(Table.SelectRows(Utilisateurs, each [#"lettre"] = "P")),
    
    MatchingDictionary = List.Buffer(List.Transform(FilteredUtilisateurs, each [#"USERS."])),
  
    AddCustomColumn = Table.AddColumn(#"Type modifié2", "PARTENAIRE", each
        let
            codeOrg = [#"Code"],
            MatchingRows = List.Select(MatchingDictionary, each Text.Contains(_, codeOrg)),
            CountRows = List.Count(MatchingRows)
        in
            if CountRows > 0 then CountRows else "NO"
    )
in
    AddCustomColumn

——————————————————————————————————————————————————

If my answer helps you solve the problem, please accept my answer as a solution and let it be seen by more people in need.

Best regards,

Mengmeng Li