How to apply List for Dynamics Column Names in subsequence steps

[M001]

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 column names since they are dynamic.

However, I am stuck when trying to replace my original M code below with the list (Years) that I created earlier, 

= Table.TransformColumnTypes(#"Promoted Headers",{{"Region", type text}, {"Country", type text}, {"Item", type text}, {"Price", type text}, {"Color", type any}, {"2024", Int64.Type}, {"2025", Int64.Type}, {"2026", Int64.Type}})

I have tried to replace 

{"2024", Int64.Type}, {"2025", Int64.Type}, {"2026", Int64.Type}

to 

{Year, Int64.Type}

but unfortunately, it does not work. Hope to get some guidance here after hours of searching with no similar answers found, thank you

Regards,

M

[ronrsnfld · Accepted solution]

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]

Hi Ron,

Thank you so much and this is exactly what I was confused about. In fact, I have found a similar solution here

https://goodly.co.in/dynamic-data-types-power-query/

Regards,

M

[JasperDJ]

Hi @M001 ,

Do you mean you have got something like this:

And then want to use something like Transpose:

And after that promote your headers?

[jennratten]

Hello!  Here is a custom function you can use to detect the column types without specifying column names.  Add a new blank query, go to the Advanced Editor and replace the contents with the custom function script below.  Name the query fnDetectDataTypes.  Then replace your Table.TransformColumnTypes step with this:

= fnDetectDataTypes(#"Promoted Headers")

Custom Function

  // ****************************************************************************************************************/
  // fnDetectDataTypes 
  // ****************************************************************************************************************/
  //   PURPOSE
  //   - Detect data types from a table's data and transform to the appropriate type
  // 
  //   LIMITATIONS
  //   - 
  //
  //   DEVELOPER (Adapted from)
  //   - https://www.thebiccountant.com/2020/05/01/detect-change-types-of-all-columns-in-power-query/
  // ****************************************************************************************************************/

let
  fn = (table as table, optional first_n_records as nullable number, optional culture as nullable text) as table =>
let
    TextColumns = Table.ColumnsOfType ( table, {type nullable text} ),
    TempKey = "--(^_^)--",
    ReplaceNulls = Table.ReplaceValue ( table, null, TempKey, Replacer.ReplaceValue, TextColumns),
    InvalidTypes = {type list, type record, type table, type function, type type, type null, type duration},
    Culture = if culture = null then "en-US" else culture,
    TopRows = if first_n_records = null then 200 else top_records, //set default to 200 rows to establish a column type
    TopNRows = Table.FirstN(ReplaceNulls, TopRows),
    ColumnNameList = Table.ColumnNames(TopNRows),
    ColumnDataLists = List.Accumulate(ColumnNameList, {}, (accumulated, i) => accumulated & {Table.Column(TopNRows, i)}),
    ColumnTypes = List.Transform(ColumnDataLists, (i) => List.ItemType(i)),
    TransformList = List.Select(List.Zip({ColumnNameList, ColumnTypes}), (i) => not List.Contains(List.Transform(InvalidTypes, (j) => Type.Is(i{1}, j)), true)),
    TypedTable = Table.TransformColumnTypes(ReplaceNulls, TransformList, Culture),
    List.ItemType = (list as list) =>
        let
            ItemTypes = List.Transform(
                list,
                each
                if Value.Type(Value.FromText(_, Culture)) = type number
                then
                    if Text.Contains(Text.From(_, Culture),"%") 
                    then Percentage.Type
                    else 
                        if Text.Length(Text.Remove(Text.From(_, Culture), {"0".."9"} & Text.ToList("., -+eE()/'"))) > 0
                        then Currency.Type
                        else 
                            if Int64.From(_, Culture) = Value.FromText(_, Culture) 
                            then Int64.Type
                            else type number
                else Value.Type(Value.FromText(_, Culture))
            ),
            ListItemType = Type.Union(ItemTypes)
        in
            ListItemType
in
    let
        //RemoveInvalidTypes = Table.RemoveColumns ( TypedTable, Table.ColumnsOfType ( TypedTable, {type list, type record, type table, type function} ) ),
        // dataflows currently converts all dates to datetime
        PrimitiveTypes = Table.ColumnsOfType(TypedTable, {type nullable number, type nullable text, type nullable logical, type nullable datetime}),
        NonConformingTypes = List.RemoveMatchingItems ( Table.ColumnNames ( TypedTable ), PrimitiveTypes ),
        NonConformingTypesToText = Table.TransformColumnTypes( TypedTable, List.Zip( { NonConformingTypes,
        List.Repeat( {type text}, List.Count( NonConformingTypes ) ) } ) ),
        TextColumnsNew = Table.ColumnsOfType (NonConformingTypesToText, {type nullable text} ),
        ReplaceTempKey = Table.ReplaceValue(NonConformingTypesToText ,TempKey,"",Replacer.ReplaceValue, TextColumnsNew )
    in
        ReplaceTempKey
in
  fn

[M001]

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]

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.

[JasperDJ]

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]

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