locked
Questions about Table.Buffer RRS feed

  • Question

  • Hi team,

    Just had an interesting situation as below.

    I have some information about cost centers (about 1000 cost centers) and they are in row hierarchy format (the maximum level of hierarchy in this case is 9):

    I want to change the data into column hierarchy format as below:

    In order to to do this, I create a list from original data with all lowest level cost centers and then look through relevant information from the original table.

    However, as this data source is simple (1 table only) not very big (about 1200 rows), the process is fairly slow (wait more than 4 mins to get the final result). My first thinking is to use Table.Buffer to load the original table (as a map table) into RAM and improve calculation speed.

    While the result is not let me happy at all. The buffer method does increase the speed while it is not fast (around 3 mins something).

    My question is, does buffer function work like load data into VBA array and store in RAM and improve calculation speed? Or there is something deeper I have not discovered yet?

    Below is the code I used in this case:

    let
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        #"Changed Type" = Table.TransformColumnTypes(Source,{{"Cost Center", type text}, {"Cost Centre Name", type text}, {"Level One code", type text}, {"Level One Name", type text}, {"Level Two Code", type text}, {"Level Two Name", type text}, {"Level Three Name", type text}, {"Level Four Name", type text}, {"Person responsible", type text}, {"Cost Center2", type text}, {"Cost Centre Name3", type text}, {"Format String", type text}}),
        #"Added Custom" = Table.AddColumn(#"Changed Type", "Hierarchy Level", each if Text.PositionOf([Format String],"[")<0 then 0 else
    (Text.PositionOf([Format String],"[")-5)/2),
        #"Added Index" = Table.AddIndexColumn(#"Added Custom", "Index", 0, 1),
        #"Removed Other Columns" = Table.SelectColumns(#"Added Index",{"Index", "Hierarchy Level", "Cost Center", "Cost Centre Name"}),
        #"Uppercased Text" = Table.TransformColumns(#"Removed Other Columns",{{"Cost Centre Name", Text.Upper}, {"Cost Center", Text.Upper}}),
        #"Trimmed Text" = Table.TransformColumns(#"Uppercased Text",{{"Cost Centre Name", Text.Trim}, {"Cost Center", Text.Trim}}),
        #"Changed Type1" = Table.TransformColumnTypes(#"Trimmed Text",{{"Hierarchy Level", Int64.Type}, {"Index", Int64.Type}}),
        lnmax=List.Max(#"Changed Type1"[Hierarchy Level]),
        cclist=Table.SelectColumns(Table.SelectRows(#"Changed Type1",each [Hierarchy Level]=0),{"Cost Center","Index"}),
        tb =Table.AddIndexColumn(cclist, "CIndex", 0, 1),
        sr=(x,y)=>
        let
        
        hnmax=
        List.Last
        (
        Table.SelectRows
        (
        Table.SelectRows(#"Changed Type1", each [Index]<x),
        each [Hierarchy Level]<>0
        )[Hierarchy Level]
        ),

        fn=
        if y<=hnmax then

        List.Last
        (
        Table.SelectRows
        (
        Table.SelectRows(#"Changed Type1", each [Index]<x),
        each [Hierarchy Level]=y
        )[Cost Center]
        )

        else null

        in
        fn,
        
        ntb=List.Generate
        (
        ()=>[n=0,tbl=tb],
        each [n]<=lnmax,
        each 
        [
        n=[n]+1,
        tbl=
        Table.AddColumn([tbl],Text.From("LEVEL "& Text.From(n)),each sr([Index],n))
        ],
        each [tbl]
        ),
    ntb1=List.Last(ntb)   





    in
        ntb1

    Cheers,

    Tom Sun


    Friday, September 8, 2017 12:57 PM

Answers

  • Hi Tom,

    I wish I could tell when Table.Buffer works but I can't.

    BUT to my experience you should use it for everything that you feed into List.Generate. So in your example it would be:

    lnmax= List.Buffer( List.Max(#"Changed Type1"[Hierarchy Level])),

      tb = Table.Buffer (Table.AddIndexColumn(cclist, "CIndex", 0, 1) ) ,


    Imke Feldmann - MVP Data Platform (PowerBI) - http://www.TheBIccountant.com 

    Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful - Thanks!

    • Marked as answer by Tom J Sun Sunday, September 10, 2017 1:42 PM
    Sunday, September 10, 2017 6:03 AM

All replies

  • Hi Tom,

    I wish I could tell when Table.Buffer works but I can't.

    BUT to my experience you should use it for everything that you feed into List.Generate. So in your example it would be:

    lnmax= List.Buffer( List.Max(#"Changed Type1"[Hierarchy Level])),

      tb = Table.Buffer (Table.AddIndexColumn(cclist, "CIndex", 0, 1) ) ,


    Imke Feldmann - MVP Data Platform (PowerBI) - http://www.TheBIccountant.com 

    Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful - Thanks!

    • Marked as answer by Tom J Sun Sunday, September 10, 2017 1:42 PM
    Sunday, September 10, 2017 6:03 AM
  • Hi Imke,

    Amazing work.

    With buffer all the elements brought into list.generate, the speed is way beyond its original performance and works really well.

    Cheers,

    Tom Sun

    Sunday, September 10, 2017 1:43 PM
  • Hi Imke,

    One more thing to mentioned about this is:

    With buffer relevant elements into list.generate, the amount of RAM used (which can be seen in task manager) is also significantly reduced (about 60% reduced comparing with the method without buffer)

    Great knowledge to know...

    Cheers,

    Tom Sun

    Sunday, September 10, 2017 1:46 PM