Forum Discussion
Capture column names and data types from an earlier step in a query
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
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
Community ChampionHi 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
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.