CODEWITHSUNDEEP
powerbipowerquery
Rasec Malkic
Rasec Malkic

Reputation: 659

How to enhance performance of tables combination?

I have a list of tables (in actual data) with different columns for which, after to combine, I get a table of 15 columns. In actual data, the list of tables is get from several previous steps and each step takes less than a second, but only Table.Combine() takes almost 2 minutes with an input of about 1200 rows. In order to show the example, I show below an output of 4 columns only,

Is there a faster alternative way to get the same output given by Table.Combine()? Thanks for any help.

This is the code of the query I has so far.

let
    Tables = {
        Table.FromRecords({[Name = "Bob", Phone = "123-4567"],
                           [Name = "",Phone = ""]
                          }),
        Table.FromRecords({[Fax = "987-6543", Phone = "838-7171"],
                           [Fax = "", Phone = "233-687"],
                           [Fax = "", Phone = "544-778"]
                         }),
        Table.FromRecords({[Cell = "543-7890"],
                           [Cell = ""],
                           [Cell = ""]
                          })
    },
    CombinedTable = Table.Combine(Tables)
in
    CombinedTable

The current output is:

enter image description here

UPDATE

This is the entire query, with Table.Buffer() added in step group5

let
     Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jVNdb4JAEPwvPFty3KHUR1C4Sg+0x5lqqSF+tGlqH5q0mv787hkal41FEsLNLjczu5NQlk6kpqP7yixnceU5Pad+Vr3SCQF4qI4A9FE9AiBQXcyj6qTWlBkDYKiOSd1C8wkNTyPpdK174DntHpzscUucB8R5iOqE6rU6B1ecOXEeEueOUXmE5nejBS1uCZHt6C5JnCge3mReiiMLJ/mNELidARgZreMCNTWAsQozPMgC0N2DFbHK8unUTTOr9XxgTGzzuVIn9AKtRZrDe7M5uod3xrj7uf8I2MD9Or7u1t9rxA2VmsJRGIPui//vX/CK8rOX7zHLFXBgbntM9L+T01koSUaRnvQNiciEanw1Ir37gSLZ7mx/Uw/qcbFvDKjjFD7Bijq1Eywf64uC87egWwzS/xP3hmfG6hc=", BinaryEncoding.Base64), Compression.Deflate)),
    let
      _t = ((type nullable text) meta [Serialized.Text = true])
    in
      type table [COL1 = _t, COL2 = _t, COL3 = _t, COL4 = _t]
  ),
  
    fx = each not List.IsEmpty(List.RemoveItems(_,{"",null})),
    
    group0 = Table.Group(Source, "COL2", {"n", each _}, 0, (x, y) => Byte.From(y = "" or y = null)), 
    group1 = Table.TransformColumns(
      group0, 
      {
        "n", 
        each 
          let
            a = Table.Skip(_), 
            b = Table.FirstN(a, each [COL3] = "" or [COL3] = null), 
            c = Table.Skip(a, Table.RowCount(b))
          in
            [a = a, b = b, c = c]
      }
    ), 
    group2 = Table.TransformColumns(
      group1, 
      {"n", each Table.ToColumns(Table.Transpose([b])) & Table.ToColumns([c])}
    ), 
    group3 = Table.TransformColumns(group2, {"n", each List.Select(_, fx)}), 
    group4 = Table.TransformColumns(group3, {"n", each Table.FromColumns(_)}), 
    group5 = Table.Buffer( Table.TransformColumns(group4, {"n", each Table.PromoteHeaders(_)})  ) , 
    combine = Table.Combine(group5[n]), 
    Custom1 = Table.SelectRows(combine, each fx(Record.ToList(_)))
in
  Custom1

The purpose of this query is to tabulate data that appears in repeated blocks and subblock in the way I show below.

enter image description here

This is the output given by the query.

enter image description here

Upvotes: 0

Views: 325

Answers (1)

horseyride
horseyride

Reputation: 21318

No, but try wrapping the initial table definitions as you go along in Table.Buffer()

let
a= Table.Buffer(Table.FromRecords({[Name = "Bob", Phone = "123-4567"],[Name = "",Phone = ""]})),
b= Table.Buffer(Table.FromRecords({[Fax = "987-6543", Phone = "838-7171"], [Fax = "", Phone = "233-687"],[Fax = "", Phone = "544-778"]})),
c= Table.Buffer(Table.FromRecords({[Cell = "543-7890"],[Cell = ""],[Cell = ""]})),
CombinedTable = Table.Combine({a,b,c})
in  CombinedTable

Upvotes: 1

Related Questions