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

Oh but I see that you'd have to hard code row values into the alternate table. But if you have just a list (or a single column table) of row values for whatever you want each of the alternate table columns to display (say it's a query named Replacements). Then do your whole original Table1 query, say the last step is FinalStep. But at the end, refer to whichever step you want to refer to which you know is before the error  (we'll say StepBeforeError).

 

GoodTable = Table.Schema(StepBeforeError)[[Name],[TypeName]],

TableTypes = Table.AddColumn(GoodTable, "AltTableTypes", each [Name]&"="&[TypeName], type text),

ListOfTypes = 

LinesFromText(TableTypes[AltTableTypes], ",")
NewTable = #table(type table [ListOfTypes],  {Replacements})

CatchError = try FinalStep otherwise NewTable

in

CatchError


--Nate