Dynamic Columns

[Certified]

Hi,

I am new to Power Query (like just started yesterday new), and I have a question.

Do the queries I create and update have to have the same number of columns?

I have these financial reports I receive every month/quarter that requires a lot of clean-up. But there is a column for each month/ quarter, which means the number of columns are never the same. Will this cause me problems in Power Query? I can't find anything on dynamic columns.

 

[johnny51981]

Are you able to create a template in the file/table that you are needing to clean up that contains the future months/quarters, and then paste in each month as the updated reports come in? That way your clean-up steps are already in place for all.

But to answer your questions, Columns are not able to be dynamic (as far as I am aware). But a way to make this easier is, within the Source step, it will show "[Delimiter=",", Columns=81, Encoding=1252, QuoteStyle=QuoteStyle.None]) as table)" toward the end. Change the Column Number, adding in the count of added columns since your last update. Then, add in the Column Header Names within any clean up steps that follow.

 

[Alex Blakenburg]

Do the queries I create and update have to have the same number of columns?

I have these financial reports I receive every month/quarter that requires a lot of clean-up. But there is a column for each month/ quarter, which means the number of columns are never the same. Will this cause me problems in Power Query? I can't find anything on dynamic columns.

Yes you can make columns dynamic.
You do want to have the formula bar turned on and become aware of which steps are hard coding column names.

In the scenario you are describing, what typically happens is that you need to unpivot the columns with the values in them.
Since your unpivoting "Other Columns" the step only hard codes the columns that will repeat every month (the row labels)
Where you tend to come undone is that that Power Query automatically adds a Changed type step after the Source step and this hard codes all the columns.
Simply delete that changed type step.

M Code before deleting Changed Type Step:

let
    Source = Excel.CurrentWorkbook(){[Name="tbl_FinRpt"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Account", Int64.Type}, {"Department", type text}, {"Jan-21", Int64.Type}, {"Feb-21", Int64.Type}, {"Mar-21", Int64.Type}, {"Apr-21", Int64.Type}, {"May-21", Int64.Type}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Account", "Department"}, "Attribute", "Value")
in
    #"Unpivoted Other Columns"

M Code after deleting Changed Type step (variable columns not hard coded):

let
    Source = Excel.CurrentWorkbook(){[Name="tbl_FinRpt"]}[Content],
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Account", "Department"}, "Attribute", "Value")
in
    #"Unpivoted Other Columns"