Power Query dynamic columns

%3CLINGO-SUB%20id%3D%22lingo-sub-2438636%22%20slang%3D%22en-US%22%3EPower%20Query%20dynamic%20columns%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2438636%22%20slang%3D%22en-US%22%3EI%20need%20to%20create%20a%20budget%20file%20with%20sales%20quantities%2C%20which%20I%20need%20to%20multiply%20with%20a%20different%20unit%20of%20measurement.%3CBR%20%2F%3EThe%20base%20file%20has%205%20years%20of%20data%20split%20by%20month.%20From%20this%20file%20I%20just%20need%201%20year%20(12%20columns).%20To%20keep%20it%20flexible%2C%20I%20have%20created%20a%20list%20with%20column%20names%2C%20that%20I%20need%20for%20the%20project.%20This%20list%20is%20called%20%22SelectedHeaders%22.%20This%20column%20name%20list%20is%20used%20to%20select%20the%20columns%2C%20groupings%2C%20roundings%20etc.%3CBR%20%2F%3EThe%20quantity%20fields%20are%20called%20%22Forecast%20-%20Month%20Year%22%20and%20the%20multiply%20column%20is%20called%20%22ReportingActivity%22.%3CBR%20%2F%3EI%20want%20to%20replace%20the%20values%2C%20so%20I%20used%20the%20following%20formula%20which%20is%20working%3A%3CBR%20%2F%3E%3D%20Table.ReplaceValue(%23%22Changed%20Type%22%2C0%2Ceach%20%5BReportingActivity%5D%2C(a%2Cb)%20%3D%26gt%3B%20a*b%20%2C%7B%22Forecast%26nbsp%3B%20-%20Jan%2022%22%7D)%3CBR%20%2F%3EThe%20question%20is%2C%20how%20can%20I%20use%20the%20%22SelectedHeaders%22%20list%20instead%20of%20fixing%20the%20column%20names%20like%20%7B%22Forecast%26nbsp%3B%20-%20Jan%2022%22%7D%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2438636%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EBI%20%26amp%3B%20Data%20Analysis%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2438842%22%20slang%3D%22en-US%22%3ERe%3A%20Power%20Query%20dynamic%20columns%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2438842%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1076576%22%20target%3D%22_blank%22%3E%40Andre_nl%3C%2FA%3E%26nbsp%3BSo%20you%20have%2060%20columns%20of%20data%20(5%20years%2C%2012%20months%20each)%3F%20Perhaps%20better%20to%20unpivot%20these%20columns%20first%20and%20then%20do%20your%20analysis%2Fcalculations%20based%20on%20the%20selected%20periods.%20But%20perhaps%20I%20misunderstood%20your%20question%20altogether.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2438967%22%20slang%3D%22en-US%22%3ERe%3A%20Power%20Query%20dynamic%20columns%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2438967%22%20slang%3D%22en-US%22%3EI%20have%20tried%20that%20on%20a%20payroll%20file%20with%20350%20employees.%2015-20%20lines%20per%20employee%20and%20roughly%2050%20columns%2C%20but%20the%20issue%20is%20that%20that%20route%20is%20just%20to%20slow%20to%20work%20with%20and%20on%20updating%20the%20data.%3CBR%20%2F%3EThe%20current%20file%20is%20actually%2072%20month%20colums%2B%2010%20or%20so%20for%20item%2C%20item%20classifications%20and%20sales%20regions.%20Number%20of%20lines%20in%20the%20database%20is%20over%2050.000.%20(It%20is%20a%20csv%20file%20of%20approx%2020Mb)%3CBR%20%2F%3EAfter%20the%20calculations%2C%20I%20need%20to%20unpivot%20this%20dataset%20again%20as%20I%20need%20the%20months%20as%20columns.%3CBR%20%2F%3EI%20select%20the%20required%20columns%20based%20on%20the%20headerlist%20and%20it%20works%20fine.%20Also%20used%20the%20headlist%20to%20round%20and%20change%20the%20colums%20types%2C%20but%20on%20I%20cant%20get%20it%20to%20work%20on%20this%20step.%3CBR%20%2F%3E%3C%2FLINGO-BODY%3E
New Contributor
I need to create a budget file with sales quantities, which I need to multiply with a different unit of measurement.
The base file has 5 years of data split by month. From this file I just need 1 year (12 columns). To keep it flexible, I have created a list with column names, that I need for the project. This list is called "SelectedHeaders". This column name list is used to select the columns, groupings, roundings etc.
The quantity fields are called "Forecast - Month Year" and the multiply column is called "ReportingActivity".
I want to replace the values, so I used the following formula which is working:
= Table.ReplaceValue(#"Changed Type",0,each [ReportingActivity],(a,b) => a*b ,{"Forecast  - Jan 22"})
The question is, how can I use the "SelectedHeaders" list instead of fixing the column names like {"Forecast  - Jan 22"}
2 Replies

@Andre_nl So you have 60 columns of data (5 years, 12 months each)? Perhaps better to unpivot these columns first and then do your analysis/calculations based on the selected periods. But perhaps I misunderstood your question altogether. 

I have tried that on a payroll file with 350 employees. 15-20 lines per employee and roughly 50 columns, but the issue is that that route is just to slow to work with and on updating the data.
The current file is actually 72 month colums+ 10 or so for item, item classifications and sales regions. Number of lines in the database is over 50.000. (It is a csv file of approx 20Mb)
After the calculations, I need to unpivot this dataset again as I need the months as columns.
I select the required columns based on the headerlist and it works fine. Also used the headlist to round and change the colums types, but on I cant get it to work on this step.