Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Dynamically return all column names

Hi

In power query i have the following which dynamically reads the column names for outputting. Im just wondering if there is a cleaner solution whereby i dont have to transform each column but rather leave it as transform all columns in list. This will allow my source to have more columns than it does at present. Also can it determine the type of each column? Appreciate any help or advice on this as im new to Power BI

 

let
Source = Csv.Document(Web.Contents("http:xxxxx"),[Delimiter=",", Columns=18, Encoding=1252, QuoteStyle=QuoteStyle.None]),
#"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
DynamicNameHeader = Table.ColumnNames (#"Promoted Headers"),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{Table.ColumnNames (#"Promoted Headers"){0}, type text}, {Table.ColumnNames (#"Promoted Headers"){1}, type number}, {Table.ColumnNames (#"Promoted Headers"){2}, type number}, {Table.ColumnNames (#"Promoted Headers"){3}, type number}, {Table.ColumnNames (#"Promoted Headers"){4}, type number}, {Table.ColumnNames (#"Promoted Headers"){5}, type number}, {Table.ColumnNames (#"Promoted Headers"){6}, type number}, {Table.ColumnNames (#"Promoted Headers"){7}, type number}, {Table.ColumnNames (#"Promoted Headers"){8}, type number}, {Table.ColumnNames (#"Promoted Headers"){9}, type number}, {Table.ColumnNames (#"Promoted Headers"){10}, type number}, {Table.ColumnNames (#"Promoted Headers"){11}, type number}, {Table.ColumnNames (#"Promoted Headers"){12}, type number}, {Table.ColumnNames (#"Promoted Headers"){13}, type number}, {Table.ColumnNames (#"Promoted Headers"){14}, type number}, {Table.ColumnNames (#"Promoted Headers"){15}, type number}, {Table.ColumnNames (#"Promoted Headers"){16}, type number}, {Table.ColumnNames (#"Promoted Headers"){17}, type number}})
in
#"Changed Type"

  • Anonymous's avatar
    Anonymous
    6 years ago

     

    I hadn't noticed. Try it this way (or a differente combination of text and number :-)):

     

    #"Changed Type"=Table.TransformColumnTypes(Source, {DynamicNameHeader{0}, type text}& List.Transform(List.Skip(DynamicNameHeader,1), each {_, type number}))

  • Anonymous's avatar
    Anonymous
    6 years ago

    Got it!....If i just remove Columns=18, it works then...thanks for your help

7 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable

    try to adapt this

     

    #"Changed Type"=Table.TransformColumnTypes(youTable, List.Transform(DynamicNameHeader, each {_, type number}))

    • Anonymous's avatar
      Anonymous
      Not applicable

      Thanks ....appears to be fine for all columns except the first one which is text and not a number....any solution. Do we need to specify the type or could it be determined by BI

      • Anonymous's avatar
        Anonymous
        Not applicable

         

        I hadn't noticed. Try it this way (or a differente combination of text and number :-)):

         

        #"Changed Type"=Table.TransformColumnTypes(Source, {DynamicNameHeader{0}, type text}& List.Transform(List.Skip(DynamicNameHeader,1), each {_, type number}))