Forum Discussion

siredman's avatar
siredman
Icon for Microsoft Employee rankMicrosoft Employee
3 years ago

Reordering non-table queries in a DataMart generates an invalid query.

I have a table which has two simialar steps. It calcualets the max of one column, then adds a row which is value of each row divided by the max. I then do the same with a second column.

 

For example, my query looks a little like this:

let
  Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTJXitWJVjICsiyUYmMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [a = _t, b = _t]),
  #"Changed column type" = Table.TransformColumnTypes(Source, {{"a", Int64.Type}, {"b", Int64.Type}}),
  MaxA = Record.Field(Table.Max(#"Changed column type", "a"), "a"),
  #"A percent" = Table.TransformColumnTypes(Table.AddColumn(#"Changed column type", "A percent", each [a] / MaxA), {{"A percent", type number}}),
  MaxB = Record.Field(Table.Max(#"A percent", "b"), "b"),
  #"B percent" = Table.TransformColumnTypes(Table.AddColumn(#"A percent", "B percent", each [b] / MaxB), {{"B percent", type number}})
in
  #"B percent"

 

Which renders like this:

 

I decided I woud like to group the calculations for an easier read, so I drag and drop MaxB to be above A percent, grouping the max calculations and percentage calculations:

 

However, this generates broken M:

let
  Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTJXitWJVjICsiyUYmMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [a = _t, b = _t]),
  #"Changed column type" = Table.TransformColumnTypes(Source, {{"a", Int64.Type}, {"b", Int64.Type}}),
  MaxA = Record.Field(Table.Max(#"Changed column type", "a"), "a"),
  MaxB = Record.Field(Table.Max(MaxA, "b"), "b"),
  #"A percent" = Table.TransformColumnTypes(Table.AddColumn(#"Changed column type", "A percent", each [a] / MaxB), {{"A percent", type number}}),
  #"B percent" = Table.TransformColumnTypes(Table.AddColumn(MaxA, "B percent", each [b] / #"A percent"), {{"B percent", type number}})
in
  #"B percent"

 

Notice that the MaxB calculation now refers to the non-tabular data MaxA, and the B percent column is trying to add a column to the same non-tabular item.

No RepliesBe the first to reply