PowerQuery Year Month as headers / optimzation.

[joshua3m]

Hello, 

Maybe someone will have better solution to my issue ;  

I have table in excel which I'm pulling to PowerQuery as i need unpivot it and here I have 2 issues.    

1. Part of the table have rolling weeks and years ; this mean that every week my headers will change and so powerquery unpivot will stop working as it will be not able to find collumns with last week header.  
Right now I have static headers which are not changing ; following unpivot i'm linking table in powerquery with another table which have correct headers but this is super slow on 2 million records.  

2. Refresh on above setup is super slow ; i have circa 3000 rows and 600 collumns which flattening is super slow ; i did try find some solution but i didn't manage find any ; would anyone recomend anything ? 

Thanks, 

Josh 

[AlexisOlson]

1. You have the option to specify which columns to unpivot (Table.Unpivot) or which columns not to unpivot (Table.UnpivotOtherColumns). If you have a changing list of columns, then use unpivot other columns.

2. If the table is small enough to fit into memory, then applying Table.Buffer before unpivoting might speed things up.