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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
bchager6
Super User
Super User

Help with M code

Hi all,

 

I have a column named "version_orig" that I would like a conditional column named "version" to refer to when populating values in it.

 

Here's the logic: If the value in a row within the version_orig column is null, then determine the maximum value in the version_orig column, add 1, then populate that value in the version column, else retrieve the value in the version_orig column and populate that in the version column.

Here's how it should look:

 

version_origversion
null37
null37
null37
3636
3535
3434

 

The below expression is not working. The error message is "[Expression.Error] We cannot apply field access to the type Function."

 

if [version_orig] = null then List.MaxN[version_orig] + 1 else [version_orig]

Any help here will be appreciated!

1 ACCEPTED SOLUTION
edhans
Super User
Super User

The formula you need is this:

 

 

if [version_orig] = null then List.Max(#"Changed Type"[version_orig]) + 1 else [version_orig]

 

 

Full code:

 

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WUtJRMjZXitXBzjI2A7HNIGxTENsUwjYBsU2UYmMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [version_orig = _t, version = _t]),
    #"Replaced Value" = Table.ReplaceValue(Source,"",null,Replacer.ReplaceValue,{"version_orig"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Replaced Value",{{"version_orig", Int64.Type}, {"version", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each if [version_orig] = null then List.Max(#"Changed Type"[version_orig]) + 1 else [version_orig])
in
    #"Added Custom"

 

 

edhans_1-1617897993055.png

 

List.Max is a function and requires parens, and you want to use the list from the table, which is the previous step - thus #"Changed Type" in my case. You cannot use List.Max with a field like you did.

How to use M code provided in a blank query:
1) In Power Query, select New Source, then Blank Query
2) On the Home ribbon, select "Advanced Editor" button
3) Remove everything you see, then paste the M code I've given you in that box.
4) Press Done
5) See this article if you need help using this M code in your model.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

View solution in original post

8 REPLIES 8
Anonymous
Not applicable

let
    Origine = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WUtJRMjZXitXBzjI2A7HNIGxTENsUwjYBsU2UYmMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [version_orig = _t, version = _t]),
    #"Rimosse colonne" = Table.RemoveColumns(Origine,{"version"}),
    #"Modificato tipo" = Table.TransformColumnTypes(#"Rimosse colonne",{{"version_orig", Int64.Type}}),
    #"Sostituito valore" = Table.ReplaceValue(#"Modificato tipo",null,List.Max(#"Modificato tipo"[version_orig])+1,Replacer.ReplaceValue,{"version_orig"})
in
    #"Sostituito valore"
edhans
Super User
Super User

The formula you need is this:

 

 

if [version_orig] = null then List.Max(#"Changed Type"[version_orig]) + 1 else [version_orig]

 

 

Full code:

 

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WUtJRMjZXitXBzjI2A7HNIGxTENsUwjYBsU2UYmMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [version_orig = _t, version = _t]),
    #"Replaced Value" = Table.ReplaceValue(Source,"",null,Replacer.ReplaceValue,{"version_orig"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Replaced Value",{{"version_orig", Int64.Type}, {"version", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each if [version_orig] = null then List.Max(#"Changed Type"[version_orig]) + 1 else [version_orig])
in
    #"Added Custom"

 

 

edhans_1-1617897993055.png

 

List.Max is a function and requires parens, and you want to use the list from the table, which is the previous step - thus #"Changed Type" in my case. You cannot use List.Max with a field like you did.

How to use M code provided in a blank query:
1) In Power Query, select New Source, then Blank Query
2) On the Home ribbon, select "Advanced Editor" button
3) Remove everything you see, then paste the M code I've given you in that box.
4) Press Done
5) See this article if you need help using this M code in your model.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

First; thank you. This looks to be the solution after I work through an error I'm receiving. 

I copied rows 13-17 that were produced from Query1, after my "Renamed Columns" step, in to the advanced editor for my query:

bchager6_0-1617906901676.png

But it's producing an error; "Expression.Error: The field 'version_orig' of the record wasn't found."

 

I noticed that when I click on the last applied step in my query, the version column, which I renamed version_orig, had its name reverted back to version.

 

Any thoughts there?

I think this is your error:

edhans_1-1617907799944.png

 

In your #"Changed Type" step (not shown, so on rows 2-11 somewhere, there is no version_orig column and even if there was, your row 15 would be ignoring all of the code between that and this.

Change it to refer to my #"Changed  Type1" step on row 14 and it should work. Same in List.Max - the table it starts with should be #"Changed Type1"

 



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

Awesome. Thanks a million! This has been a big help.

Great @bchager6 - glad I was able to assist!



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

@bchager6 - did my solution workf or you? If so, can you mark it as the solution to show this was solved? If not, post back with questions/issues.

 

Thanks!



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting
AlexisOlson
Super User
Super User

The problem is that [version_org] is not a list that you can take a max of. Also, List.MaxN is a function, not a table or record with a field called [version_org].

 

I think you want your step to look like this:

= Table.AddColumn(
    #"Changed Type", "version", each
    if [version_orig] = null
    then List.Max(#"Changed Type"[version_orig]) + 1
    else [version_orig]
)

 

Change #"Changed Type" to whatever the name of your previous step is.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors