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

Hi,

Thank your for the answer. There is short example:

 

let
Źródło = Excel.CurrentWorkbook(){[Name="Tabela1"]}[Content],
#"Zduplikowano kolumnę" = Table.DuplicateColumn(Źródło, "Value", "Value — kopia"),
#"Zduplikowano kolumnę1" = Table.DuplicateColumn(#"Zduplikowano kolumnę", "Country", "Country — kopia"),
#"Zduplikowano kolumnę2" = Table.DuplicateColumn(#"Zduplikowano kolumnę1", "Brand Manager", "Brand Manager — kopia"),
#"Zduplikowano kolumnę3" = Table.DuplicateColumn(#"Zduplikowano kolumnę2", "Day", "Day — kopia"),
#"Zduplikowano kolumnę4" = Table.DuplicateColumn(#"Zduplikowano kolumnę3", "Month", "Month — kopia")
in
#"Zduplikowano kolumnę4"

 

I want to merge steps(#"Zduplikowano kolumnę",#"Zduplikowano kolumnę1",#"Zduplikowano kolumnę2",#"Zduplikowano kolumnę3",#"Zduplikowano kolumnę4") for one step.

 

35 sec is impossible time. I have 700,000 rows and more than 80 columns in this file.

  • Anonymous's avatar
    Anonymous
    Not applicable
    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

    You should keep the 5 columns to duplicate in a separate table.

    The duplication og these takes less than 1 minute.

    Test with the script I posted and measure the time.

    If you carry the burden of a 700kX80 table with you then yes it takes a long time ...