Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Multiple duplicating column in one step

Hello everyone! I want to optimalize big report in my company. In this report is more than 700,000 rows, so time for refreshing is very long. In 5 steps PowerQuery creates five duplicate columns(for...
Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago
    let
    
    col0 = List.Repeat({List.Random(700000,111)}, 75),
    col1 = List.Random(700000,1),
    col2= List.Random(700000,2),
    col3= List.Random(700000,3),
    col4=List.Random(700000,4),
    col5=List.Random(700000,5),
    tfc= Table.Buffer(Table.FromColumns(col0&{col1,col2,col3,col4,col5})),

    #"Duplicata colonna" = Table.DuplicateColumn(tfc, "Column1", "Column1 - Copia"),
    #"Duplicata colonna1" = Table.DuplicateColumn(#"Duplicata colonna", "Column2", "Column2 - Copia"),
    #"Duplicata colonna2" = Table.DuplicateColumn(#"Duplicata colonna1", "Column3", "Column3 - Copia"),
    #"Duplicata colonna3" = Table.DuplicateColumn(#"Duplicata colonna2", "Column4", "Column4 - Copia")

in
#"Duplicata colonna3"

     

     

    Try this. be aware of using table.Buffer before you start adding duplicates. I have seen that it adds about 1k lines / sec. So in 700sec (I locked after a few seconds, though) it should complete the lap.

Anonymous's avatar
Anonymous
Not applicable
5 years ago
let
    col1 = List.Random(700000,1),
    col2= List.Random(700000,2),
    col3= List.Random(700000,3),
    col4=List.Random(700000,4),

    tfc= Table.FromColumns({col1,col2,col3,col4}),
    #"Duplicata colonna" = Table.DuplicateColumn(tfc, "Column1", "Column1 - Copia"),
    #"Duplicata colonna1" = Table.DuplicateColumn(#"Duplicata colonna", "Column2", "Column2 - Copia"),
    #"Duplicata colonna2" = Table.DuplicateColumn(#"Duplicata colonna1", "Column3", "Column3 - Copia"),
    #"Duplicata colonna3" = Table.DuplicateColumn(#"Duplicata colonna2", "Column4", "Column4 - Copia")

in
#"Duplicata colonna3"

this query produces the final table of 700000 lines and 4 duplicate columns at the rate of 20k lines per sec. So 700k / 20k / sec = about 35sec

 

this makes me think that the problem lies elsewhere.

 

Try to explain in more detail the situation you start from, what you do and what happens ...