Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
[Note: All of the queries below are included in the attached PBIX file. I recommend opening up that file to follow along.]
As I mentioned in How to create json table for easier sharing pbix file, creating a table using the Enter Data tool results in an M code query that looks like the following [this is Table1 in the attached PBIX]:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUUoE4pKi0lQgZahvqG9kYGCoFKsTrWQEFEgC4rTEnGKQpJG+EUjSCCxpDBRIRmg01jcGyRkrxcYCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [A = _t, B = _t, C = _t, D = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"A", Int64.Type}, {"B", type text}, {"C", type logical}, {"D", type date}})
in
#"Changed Type"
That's a lot of ugly and opaque code for something that can be written more cleanly as
let
Source =
Table.FromRows(
{
{ 1, "a", true, #date(2001, 1, 1) },
{ 2, "b", false, #date(2002, 2, 2) },
{ 3, "c", true, #date(2003, 3, 3) }
},
type table [A = number, B = text, C = logical, D = date]
)
in
Source
This format doesn't have gibberish text as compressed binary data, which makes it more readable not only for fellow humans but also for change tracking / version control systems like Git.
To achieve this, I've written a custom function that converts a query into M code like the simplified example above.
let
func =
(TableName as table) as text =>
let
Source = Table.ToRows(TableName),
Indent = Text.Repeat(" ", 16),
RowSeparator = " },#(lf)" & Indent & "{ ",
Schema = Table.Schema(TableName),
ValToText = (x) =>
let
xType = Value.Type(x),
/*Hack to convert data type to plain text*/
xTypeText = Text.BeforeDelimiter(
Table.Schema(
#table(type table [Col = Value.Type(x)], {{x}})
){0}[TypeName],
"."
),
result =
if x = null then
"null"
else if xType = Text.Type or xType = Any.Type then
"""" & x & """"
else if xType = Number.Type or xType = Logical.Type then
Text.From(x)
else if xType = Date.Type then
"#date("
& Text.From(Date.Year(x)) & ", "
& Text.From(Date.Month(x)) & ", "
& Text.From(Date.Day(x))
& ")"
else if xType = Time.Type then
"#time("
& Text.From(Time.Hour(x)) & ", "
& Text.From(Time.Minute(x)) & ", "
& Text.From(Time.Second(x))
& ")"
else if xType = DateTime.Type then
"#datetime("
& Text.From(Date.Year(x)) & ", "
& Text.From(Date.Month(x)) & ", "
& Text.From(Date.Day(x)) & ", "
& Text.From(Time.Hour(x)) & ", "
& Text.From(Time.Minute(x)) & ", "
& Text.From(Time.Second(x))
& ")"
else if xType = DateTimeZone.Type then
"#datetimezone("
& Text.From(Date.Year(x)) & ", "
& Text.From(Date.Month(x)) & ", "
& Text.From(Date.Day(x)) & ", "
& Text.From(Time.Hour(x)) & ", "
& Text.From(Time.Minute(x)) & ", "
& Text.From(Time.Second(x)) & ", "
& Text.From(DateTimeZone.ZoneHours(x)) & ", "
& Text.From(DateTimeZone.ZoneMinutes(x))
& ")"
else if xType = Duration.Type then
"#duration("
& Text.From(Duration.Days(x)) & ", "
& Text.From(Duration.Hours(x)) & ", "
& Text.From(Duration.Minutes(x)) & ", "
& Text.From(Duration.Seconds(x))
& ")"
else
xTypeText & ".FromText(""" & Text.From(x) & """)"
in
result,
DataRows_Text = Text.Combine(
List.Transform(
Source,
each Text.Combine(List.Transform(_, ValToText), ", ")
),
RowSeparator
),
ColTypes_Text = Text.Combine(
List.Transform(
Table.ToRecords(Schema),
each [Name] & " = " & [Kind]
),
", "
),
Query =
"let
Source =
Table.FromRows(
{
{ " & DataRows_Text & " }
},
type table [" & ColTypes_Text & "]
)
in
Source",
StandardDataTypes = {
"binary",
"date",
"datetime",
"datetimezone",
"duration",
"logical",
"number",
"text",
"time",
"any"
},
UnhandledTypes = List.RemoveItems(Schema[Kind], StandardDataTypes),
Output =
if not List.IsEmpty(UnhandledTypes) then
"Unsupported Column Types"
else
Query
in
Output,
documentation = [
Documentation.Name = " Table_to_M ",
Documentation.Description = " Converts a table into M code text that recreates the table as query. "
]
in
Value.ReplaceType(func,Value.ReplaceMetadata(Value.Type(func), documentation))
To use this function, right-click in the Queries pane to create a new blank query.
Then rename this new query Table_2_M and paste the code above into the Advanced Editor (replacing the blank query code). [Alternatively, you can copy this function from the attached PBIX file and paste it into other query editors.]
Once this is done, you should see the function in the Queries pane prefixed with a little "fx" icon and when you click on the function, there should be a dropdown where you can choose to invoke the function on any of the other queries you have (if any) in your Queries pane.
Suppose we define the query I started the post with as Table1 so that now it shows up in the Queries pane.
We can then choose this table/query from the Enter Parameter dropdown list in the Table_To_M and click Invoke. The result should be a new query called Invoked Function that outputs M code text that looks like this:
If you paste this code into the Advanced Editor of a new or existing query, it should return the same table as the ugly code at the start of this post.
Let me know what you think or if you run into any errors I didn't come across in testing.
--Alexis O.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.