Query Speed - Recombining grouped tables

[Syndicate_Admin]

Dear all

For preface I have to say, that I am not at all a programmer nor do I know my way around databases particularly well. I am using Power Query in Excel to structure and rearrange tables with different hierarchies.

To summarize my problem: I am able to cobble together some working code that archieves all that I want, but it renders the query incredibly slow. The suspect arises that my code is overly complicated and not very efficient at what it does so I am asking for help to streamline it, or point me in a totally different direction.

To illustrate my goal let me give you a quick example: My datasource outputs a structure like this (this is very simplyfied, in my case it outputs up to 500 rows):

Hierarchy 1	Hierarchy 2	Hierarchy 3	...	Value
Group A	Group A1	Group A1.1	...	150
Group A	Group A1	Group A1.2	...	100
Group A	Group A1	Group A1.3	...	50
Group A	Group A2	Group A2.1	...	3000
Group B	Group B1	Group B1.1	...	10
Group B	Group B1	Group B1.2	...	30
Group B	Group B1	Group B1.3	...	20

I then proceed to group the entries starting from the lowes hierarchy and insert summery-rows "above" the original table. Afterwards I do some cleanup and formatting that is specific to the form of presentation witch is of no concern for me right now. The finished table might look like this.

Hierarchy 1	Hierarchy 2	Hierarchy 3	...	Value
Group A				3300
	Group A1			300
		Group A1.1	...	150
		Group A1.2	...	100
		Group A1.3	...	50
	Group A2			3000
		Group A2.1	...	3000
Group B				60
	Group B1			40
		Group B1.1	...	10
		Group B1.2	...	30
	Group B2			20
		Group B2.1	...	20

Now to my code: I made some effort to anotate but I recon it still might be confusing, please ask me to clarify if needed

let
//source and preparations
//(...)

//Group lowest hierarchy "XXX"
  #"XXX" = Table.Group(A, {"BKP einstellig", "Group", "BKP (dreistellig)"}, {{"exkl. MwSt XXX", each List.Sum([#"exkl. MwSt"]), Currency.Type}}), 

//Group second hierarchy "XX"
  #"XX" = Table.Group(#"XXX", {"BKP einstellig", "Group"}, {{"exkl. MwSt XX", each List.Sum([#"exkl. MwSt XXX"]), Currency.Type}}), 

//Group first hierarchy "X"
  #"X" = Table.Group(#"XX", {"BKP einstellig"}, {{"exkl. MwSt", each List.Sum([#"exkl. MwSt XX"]), Currency.Type}}), 

//FIRST ITERATION: INSERT EACH ROW OF SECOND HIERARCHY ABOVE ITS CORRESPONDING ROWS IN LOWEST HIERARCHY
//===================================================================================================== 
  //get count of rows for each title in second hierarchy
    #"Count XX" = Table.Group(#"XXX", {"Group"}, {{"Anzahl XXX", each Table.RowCount(_), Int64.Type}}), 
  //calculate offset of where to insert each title
    #"C_Offset XX" = Table.ReplaceValue(
      Table.AddColumn(
        #"Count XX", 
        "Offset XXX", 
        each List.Sum(List.Range(#"Count XX"[Anzahl XXX], 0, Table.PositionOf(#"Count XX", _))), 
        type number
      ), 
      null, 
      0, 
      Replacer.ReplaceValue, 
      {"Offset XXX"}
    ),
  //insert titles of second hierarchy above each set of lowest hierarchy
    #"Group BKP XX" = List.Transform(
      {0 .. Table.RowCount(#"C_Offset XX") - 1}, 
      each [
        A = Table.Range(#"XXX", #"C_Offset XX"[Offset XXX]{_}, #"C_Offset XX"[Anzahl XXX]{_}), 
        B = Table.FromRecords({XX{_}}) & A
      ][B]
    ), 
  //my attempt to speed up by buffering the result, so the next step does not need to repeat this calculation - it does not help a thing 
    #"Combine XX" = Table.Buffer(Table.Combine(#"Group BKP XX")), 

//SECOND ITERATION: INSERT EACH ROW OF FIRST HIERARCHY ABOVE ITS CORRESPONDING ROWS IN COMBINED PREVIOUS TABLE
//============================================================================================================
  //get count of rows for each title in first hierarchy
    #"Count X" = Table.Group(#"Combine XX", {"BKP einstellig"}, {{"Anzahl XX", each Table.RowCount(_), Int64.Type}}), 
  //calculate offset of where to insert each title
    #"C_Offset X" = Table.ReplaceValue(
      Table.AddColumn(
        #"Count X", 
        "Offset XX", 
        each List.Sum(List.Range(#"Count X"[Anzahl XX], 0, Table.PositionOf(#"Count X", _))), 
        type number
      ), 
      null, 
      0, 
      Replacer.ReplaceValue, 
      {"Offset XX"}
    ), 
  //insert titles of first hierarchy above each set of second and third hierarchy
    #"Group BKP X" = List.Transform(
      {0 .. Table.RowCount(#"C_Offset X") - 1}, 
      each [
        A = Table.Range(#"Combine XX", #"C_Offset X"[Offset XX]{_}, #"C_Offset X"[Anzahl XX]{_}), 
        B = Table.FromRecords({#"X"{_}}) & A
      ][B]
    ), 
    #"Combine X" = Table.Combine(#"Group BKP X") 

//cleanup an formatting
//(...)
  
in
  #"BKPleer"

[Syndicate_Admin]

Hi @mberchtold ,

Your code is pretty smart, but your use of Power Query is way off the mark I'm afraid.

You're trying to hammer Power Query into a shape it's just not designed for: it's a mashup/transformation tool, not a presentation/format tool.

Your original data format is absolutely fine as a source data structure. If you want it to look different, then I'd suggest applying the data to a pivot table which is designed to make these presentational summaries.

If you want to change how the summaries calculate you could even go so far as to create measures in your Excel file, but Power Query ain't the place for any of this.

Pete

[Syndicate_Admin]

Hello @BA_Pete 

Thanks vor your quick response and pointing me towards pivot tables.

Nevertheless I fiddled around a bit more and found that buffering my intial table reduces the load time by a ton!

Best

[Syndicate_Admin]

No problem, glad you've got it working.

If you have the time, it would be good if you could briefly post where you applied the table buffers in your code. We can then mark that as the solution to help others find help quicker if they have a similar problem (although my answer still stands as the correct one 😉 ).

Ta,

Pete

[Syndicate_Admin · Accepted solution]

In order to wrap it up:

the suggestion to work with pivot tables from @BA_Pete is - when it comes to numbers - indeed by far superior to what I am trying to archieve in power query. Though pivot tables do lack in proper usability in other regards (e.g. formatting is by far not intuitive, and the biggest obstacle is to include text in a pivot table - that needs its own set of workarounds).

What helped me with my loading time issues regarding the code I posted was to Buffer certain tables (I am not sure if I need to buffer all of them). That dramatically reduced loading times. My interpretation is that my code has a lot of iterations and - without buffering - everytime it iterates, it calls the web-source. By buffering the tables I use to iterate over, it seems to only call the web-source once and does all necessary calculations locally. Big speed increase.

See below where I inserted the buffering

let
//source and preparations
//(...)

//Group lowest hierarchy "XXX" and !!! BUFFER Table !!!             <==================
  #"XXX" = Table.Buffer(
    Table.Group(A, {"BKP einstellig", "Group", "BKP (dreistellig)"}, {{"exkl. MwSt XXX", each List.Sum([#"exkl. MwSt"]), Currency.Type}})
  ), 

//Group second hierarchy "XX"  and !!! BUFFER Table !!!             <==================
  #"XX" = Table.Buffer(
    Table.Group(#"XXX", {"BKP einstellig", "Group"}, {{"exkl. MwSt XX", each List.Sum([#"exkl. MwSt XXX"]), Currency.Type}})
  ), 

//Group first hierarchy "X"  and !!! BUFFER Table !!!             <==================
  #"X" = Table. Buffer(
    Table.Group(#"XX", {"BKP einstellig"}, {{"exkl. MwSt", each List.Sum([#"exkl. MwSt XX"]), Currency.Type}})
  ), 

//FIRST ITERATION: INSERT EACH ROW OF SECOND HIERARCHY ABOVE ITS CORRESPONDING ROWS IN LOWEST HIERARCHY
//===================================================================================================== 
  (...)

[Syndicate_Admin]

Hi @mberchtold ,

Many thanks for taking the time to include a detailed wrap-up post.

Going the extra mile for future users is both generous and commendable, and is the sort of dedication that takes this community to the next level. 👍

Pete