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

[JimJaggers]

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:

AlternativeOutput = #table(type table [Month=text,Product ID=text,Sales=Int64.Type], {{"Error", "Error", 0}})

For some context, the above statement will be used as an alternative output if previous steps in the query result in an error and the goal is create a single row dummy output table that has the same structure as the table the previous steps would have created if they had not generated an error.

However in my real application of the the above step I will be creating 20-40 columns for each of 10-15 queries using that structure.  And I'm wondering is there an easy way to capture the Name=Data Type pairs from another power query statement within the query?  I'm fine with some sort of manual capture that I can just copy-paste into my power query (e.g. generating the desired table and capturing its Name-Data Type structure).  Though an automated structure is usually better.

What I'm doing now is running an automatically detect data types query step (from the error free initial version of the query), something similar to the following:

#"Changed Type" = Table.TransformColumnTypes(Source,{{"Month", type text}, {"Product ID", type text}, {"Sales", Int64.Type}})

copying the Name-Data Type section ({"Month", type text}, {"Product ID", type text}, {"Sales", Int64.Type}), pasting it into the AlternativeOutput statement, and editing it into the proper structure ([Month=text,Product ID=text,Sales=Int64.Type]).

Certainly not an insurmountable obstacle.  But, still, I'd like an easier way.

[watkinnc · Accepted solution]

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

[watkinnc]

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 

[ImkeF]

Hi @SpartaBI ,
our solution cover different use cases: Yours need the Table1 still return correct results, while my solution covers the case where during a certain load, even that step would return an error.

[ImkeF]

Hi @JimJaggers ,
if my understanding is correct, you need a method to create a string representing the M-code to create an empty table. The function in this article provides this option: Create (empty) table string from schema in Power Query – (thebiccountant.com)
You reference your "good" table in the only function argument and will retrieve the M-code that creates an empty table with all column names and types from the original table.

let
    fnFormatted = let
    func = (myTable as table) =>
        let
            #"Table1 Schema" = Table.Schema(myTable),
            TypesList = Table.AddColumn(
                #"Table1 Schema",
                "TypeRecord",
                each [Name] & "=" & [TypeName]
            )[TypeRecord],
            TypeRecordString = "[" & Text.Combine(TypesList, ", ") & "]",
            TableString = "#table(type table "
                & TypeRecordString
                & ", {{"
                & Text.Combine(List.Repeat({"null"}, List.Count(TypesList)), ", ")
                & "}})"
        in
            TableString,
    documentation = [
        Documentation.Name = " Text.CreateEmptyTableFromSchema ",
        Documentation.Description
            = " Creates the M-code for an empty table based on a table schema. ",
        Documentation.LongDescription
            = " Creates the M-code for an empty table based on a table schema (Table.Schema). ",
        Documentation.Category = " Text.Transformations ",
        Documentation.Source = " www.TheBIcountant.com https://wp.me/p6lgsG-2tJ . ",
        Documentation.Version = " 1.0 ",
        Documentation.Author = " Imke Feldmann ",
        Documentation.Examples = {
            [
                Description = "  ",
                Code
                    = " let
    myTable = #table( type table [myText = Text.Type, myNumber = Int64.Type, myDate = Date.Type], 
 
//  myText| myNumber|            myDate| 
{//-------|---------|------------------|  
{      ""A"",       10, #date(2022, 01, 01) } } ),

    FunctionCall = fnText_CreateEmptyTableFromSchema( myTable )
in
    FunctionCall "
                  ,
                Result
                    = " #table(type table [myText=Text.Type, myNumber=Int64.Type, myDate=Date.Type], {{null, null, null}}) 
  "
   
            ]
        }
    ]
in
    Value.ReplaceType(func, Value.ReplaceMetadata(Value.Type(func), documentation))
in
    fnFormatted

[JimJaggers]

That is some fancy coding.  A bit beyond my current ability to read.  But I will use it as an exercise to learn more about coding M.

BTW, I am a pretty decent SQL coder and I think what you are doing here is the equivalent of creating dynamic SQL.  I can read most SQL directly; except for dynamic SQL, unless it is very simple.  Most of the time I have to tear the code apart and put it back together to really understand what it is doing.  I suspect that may be the case here.  I like to think I'm starting to learn how to read M; but this code may be sufficiently complex that I can't really understand without reconstructing it.

Thank you very much for this code.

[watkinnc · Accepted solution]

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

[watkinnc]

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

[SpartaBI]

Hi @JimJaggers,

Have a look at the following code

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

[JimJaggers]

@SpartaBI thank you.  This solution does appeal to me.  But I never could quite get it to work.  Do you have any idea how I could get the schema table generated by 

#"Table1 Schema" = Table.Schema(Table1)

into a format that I could use to create a table with that schema?  Here is what I tried

Columns = Table.AddColumn(TableSchema, "ColumnDefinitions", each [Name]&"="&[Kind]),//create column with Name-Data Type values
A = Table.Column(Columns, "ColumnDefinitions"),//convert Name=Data Type column into a list
B = Table.TransformColumns(Columns, {"ColumnDefinitions", each Text.Combine(_,",")}),//convert list into a CSV list

//Use CSV list to generate table
AlternativeOutput = #table(type table B,
{{"DNU","DNU","DNU","DNU","DNU",
"DNU","DNU","DNU","DNU","DNU",
0,"DNU","DNU",0,
0,0,0,
false,0,0,
0,"DNU",
false,0,
0,"DNU","DNU","DNU"
}}
),

But I get an error stating "We cannot convert a value to type Table to type Type."  I've tried a few things, such as removing the "type table" from the beginning of the #table() function and applying various conversion functions to the CSV string in #"B" or the list in #"A", but to no avail.  I think my problem is not really understanding the compund data types, particularly what a type Type is.

Any thoughts you have on the matter are appreciated, for my elucidation.  But not really necessary for me to address the problem.

Thank you again for your suggestion.

[SpartaBI · Accepted solution]

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

     

[JimJaggers]

That is a beautiful solution.