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"
JasperDJ's avatar
JasperDJ
Helper I
2 years ago

Hi M001 ,

 

Do you mean the column name always changes and is dynamic so you can't quote them in a 'Change Type'?

If so, you could try to split the column change and header promotion. If the structure of the data source stays the same, changing Column 6 through Column 8 to Int64-type, followed by dynamicly promoting the header without mentioning any column name in the first row.

 

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCkpNz8zPU9JRcs4vzSspqgSyPEtSc4FUQFFmcipYIie/CEgbGRiZQChTCGWmFBsLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t, Column5 = _t, Column6 = _t, Column7 = _t, Column8 = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", Int64.Type}, {"Column7", Int64.Type}, {"Column8", Int64.Type}}),
#"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true])
in
#"Promoted Headers"

 

 

M001's avatar
M001
Helper I
2 years ago

Hi Jasper, 

 

This is a good idea. However, I would like to know how to put the column names that I have converted to list into Table.TransformColumnTypes?

 

I convert column names for 2024, 2025, 2026 to a list named as Year, and how do I put it into Table.TransformColumnTypes? The code below doesnt work

 

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

 

 

Thank you,

M