powerbipowerquerypowerbi-desktopm

Why is this small query in Power BI using several gigabytes of memory?

I have a model with several queries in it. In particular, there are two, Major and Minor, which I would like to have a cartesian product of. Major has 38 rows and Minor has 12 rows, so their product has 456 rows. In other words, I want to build the following Table:

M1   m1
M1   .
M1   .
M1   m12
M2   m1
M2   .
M2   .
M2   m12
.
.
M38  m1
M38  .
M38  .
M38  m12

It seems straightforward; I have two lists, MajorList and MinorList. My query is:

Table = Table.Combine(
        List.Transform(
            MajorList, 
            (M) => Table.FromColumns( {List.Repeat({M}, 12), MinorList} )
        )
    )

This query works, but it uses several gigabytes of memory and takes several minutes. What am I doing wrong and what is the right way to do this?

Solution

  • Try this - it is instant for me.

    enter image description here

    let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Minor),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Name"}, {"Custom.Name"})
in
    #"Expanded Custom"