Query Speed - Recombining grouped tables

Hi Anonymous ,

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

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
//===================================================================================================== 
  (...)