Change column types dynamically

[Gerald23]

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"

[Jakinta · Accepted solution]

Your Changed Type step should be:

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

[RaiSta]

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]

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

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

[Gerald23]

Tested and this works thanks for the help 🙂