cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
bchager6
Post Patron
Post Patron

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

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

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

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

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

@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
Memorable Member
Memorable Member

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
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors