Forum Discussion

M001's avatar
M001
Helper I
2 years ago
Solved

How to apply List for Dynamics Column Names in subsequence steps

Hi there,   My last 3 columns 2024, 2025 and 2026 always change from time to time, as it represents year of my sales that I want to track, therefore I created a list (I named it as Years) for these...
Need Help
  • ronrsnfld's avatar
    ronrsnfld
    2 years ago

    If it is only the last three columns that need to have dynamic names, you can set the data types for all the columns by creating a list similar to:

     

    #"Types List" = {{"Region", type text}, {"Country", type text}, {"Item", type text}, {"Price", Currency.Type}, {"Color", type text}}
    & List.Transform(List.LastN(Table.ColumnNames(#"Promoted Headers"),3), each {_, Int64.Type}),

     

    Then you can use it in the Transform.ColumnTypes function like:

     

        #"Promoted Headers" = ...,
    
#"Types List" = {{"Region", type text}, {"Country", type text}, {"Item", type text}, {"Price", Currency.Type}, {"Color", type text}}
    & List.Transform(List.LastN(Table.ColumnNames(#"Promoted Headers"),3), each {_, Int64.Type}),
    
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers", #"Types List")
in
    #"Changed Type"
M001's avatar
M001
Helper I
2 years ago

Hi Jenn,

 

My question is more about why the column names for 2024, 2025 and 2026 that I have converted to a List (named as Year) doesnt work when I put in the following code. Thank you

 

= Table.TransformColumnTypes(#"Promoted Headers",{{Year, Int64.Type}})

 

jennratten's avatar
jennratten
Super User
2 years ago

If your 'Year' field contains a list of values, like this:

and your objective it to simply set the correct type for the column, it would not be 'Int64.Type' because your field does not contain integers - it contains a list of integers.  Therefore, you would leave it as 'type any' by omitting it from the transform column types step.