How to apply List for Dynamics Column Names in subsequence steps

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"

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?

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