Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
M001
Helper I
Helper I

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 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

1 ACCEPTED SOLUTION
ronrsnfld
Super User
Super User

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"

View solution in original post

8 REPLIES 8
ronrsnfld
Super User
Super User

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"

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
Helper I
Helper I

Hi @M001 ,

 

Do you mean you have got something like this:

JasperDJ_0-1693376676228.png

 

And then want to use something like Transpose:

JasperDJ_1-1693376720227.png

 

And after that promote your headers?

JasperDJ_2-1693376768191.png

 

jennratten
Super User
Super User

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

 

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}})

 

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

jennratten_0-1693401785107.png

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.

 

jennratten_1-1693401957303.png

 

JasperDJ
Helper I
Helper I

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"

 

JasperDJ_0-1693306744527.png

 

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

 

 

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors