Forum Discussion

JimJaggers's avatar
JimJaggers
Frequent Visitor
4 years ago
Solved

Capture column names and data types from an earlier step in a query

Is it possible to capture the column name and data type from another step in a query in a form that is fit for making a table?   I would like to run a query step similar to the following:   Alter...
Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    Yes, you can refer to any other step in the query, whether before or after the current step, and it will return a table, list, value, whatever it might be. So NewStep = ThreeStepsAgo will return ThreeStepsAgo, and if it's a table, you have a table. You can also do

     

    NewColumns = ThreeStepsAgo[[Column1], [Column5], [Column9]] to return the table step with only certain columns.

     

    --Nate

  • SpartaBI's avatar
    SpartaBI
    4 years ago

    Hi JimJaggers,

    You don't need to work hard to do that. You can simply use the existing table type. Like this:

     

     

    let
    Table1 = #table(type table [Num=Int64.Type,String=text], {{1, "One"}}),
    Table2 = #table(type table [String=text], {{"two"}}),
    AlternativeOutput = #table(Value.Type(Table1),{{0,"Zero"}}) 
in
    AlternativeOutput

     

     

     



    Showcase Report – Contoso By SpartaBI


         

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Yes, you can refer to any other step in the query, whether before or after the current step, and it will return a table, list, value, whatever it might be. So NewStep = ThreeStepsAgo will return ThreeStepsAgo, and if it's a table, you have a table. You can also do

 

NewColumns = ThreeStepsAgo[[Column1], [Column5], [Column9]] to return the table step with only certain columns.

 

--Nate

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Even easier, you can make a new query, named AltTable:

     

    Source = Table.Schema(TableName)[[Name], [TypeName]]
    NewSchema = Table.AddColumn(Source, each [Name]&"="&[TypeName], type text)

    AltFinalTable = #table(type table [AltTable], {"Error", "Error", 0})

     

    Now one more query:

     

    Source = try MainQueryName otherwise AltTable

     

    --Nate