Forum Discussion

Gerald23's avatar
Gerald23
Helper I
5 years ago
Solved

Change column types dynamically

Hi Everyone,

 

I have an issue with changing column types in the Power Query Editor.  Normally i change them in the UI but this time my column names can change and there can be more or less columns in the data then currently is the case. So i wish to change all columns (Except the first 13 columns (They are always the same)) to the Currency type (Fixed Decimal Number). After some time on google I created the code below that doesn't have any syntax errors in the advanced editor however it gives me another error after pressing Done in the advanced editor.

 

ERROR:

Expression.Error: We cannot convert a value of type Table to type List.
Details:
    Value=[Table]
    Type=[Type]

 

This is the code i use in the advanced editor that gives above error

let
    Source = Csv.Document(Web.Contents("https://*******"),[Delimiter=",", Columns=179, Encoding=65001, QuoteStyle=QuoteStyle.None]),
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{Source,
      List.Transform(
          List.RemoveFirstN(
              Table.ColumnNames(Source),
              13
    ),
      each {_, Currency.Type}
    )})
      in
    #"Changed Type"

 

  • Your Changed Type step should be:

     

    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",
          List.Transform(
              List.RemoveFirstN(
                  Table.ColumnNames(Source),
                  13
        ),
          each {_, Currency.Type}
        ))

     

4 Replies

  • Jakinta's avatar
    Jakinta
    Solution Sage

    Your Changed Type step should be:

     

    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",
          List.Transform(
              List.RemoveFirstN(
                  Table.ColumnNames(Source),
                  13
        ),
          each {_, Currency.Type}
        ))

     

    • Gerald23's avatar
      Gerald23
      Helper I

      Tested and this works thanks for the help 🙂

    • RaiSta's avatar
      RaiSta
      New Member

      Hi, great solution!

      Only, I'd like to know, how to use this 'List.Transform'-function with regional code (for instance "en-US" in case the system is not in US-american setting) - how to get this into the List.Transform-function?

       

      Thanks in advance,

      RaiSta

      • RaiSta's avatar
        RaiSta
        New Member

        ... just found the answer by chance...:

        = Table.TransformColumnTypes(>table<, List.Transform(>List-of-columnheads<, each {_, Number.Type}), "en-US")