Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
JimJaggers
Frequent Visitor

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:

 

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.

2 ACCEPTED SOLUTIONS
watkinnc
Super User
Super User

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


I’m usually answering from my phone, which means the results are visualized only in my mind. You’ll need to use my answer to know that it works—but it will work!!

View solution in original post

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

 

 

SpartaBI_0-1652353918283.png

 



Showcase Report – Contoso By SpartaBI


SpartaBI_3-1652115470761.png  SpartaBI_1-1652115142093.png   SpartaBI_2-1652115154505.png

Full-Logo11.png

View solution in original post

10 REPLIES 10
watkinnc
Super User
Super User

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 


I’m usually answering from my phone, which means the results are visualized only in my mind. You’ll need to use my answer to know that it works—but it will work!!
ImkeF
Super User
Super User

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.

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

ImkeF
Super User
Super User

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

 

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

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
Super User
Super User

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


I’m usually answering from my phone, which means the results are visualized only in my mind. You’ll need to use my answer to know that it works—but it will work!!

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


I’m usually answering from my phone, which means the results are visualized only in my mind. You’ll need to use my answer to know that it works—but it will work!!
SpartaBI
Community Champion
Community Champion

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"


SpartaBI_0-1652255343744.png

@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.

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

 

 

SpartaBI_0-1652353918283.png

 



Showcase Report – Contoso By SpartaBI


SpartaBI_3-1652115470761.png  SpartaBI_1-1652115142093.png   SpartaBI_2-1652115154505.png

Full-Logo11.png

That is a beautiful solution.

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors
Top Kudoed Authors