Forum Discussion

Mars3442's avatar
Mars3442
Helper I
2 years ago

Replace the blank value based on some factor

Hi everyone,

I want to ask a help for solving my problem

I have a table like this

I need to replace the null value with these conditional :

If they have same Code 1 and Description, please fill the same Code 2

 

My expectation table like this

 

What M Code I should write to get my expectations?

 

Please help me to solve this problem

 

Thank you..

Need Help

1 Reply

  • tackytechtom's avatar
    tackytechtom
    Most Valuable Professional
    2 years ago

    Hi Mars3442 ,

     

    One potential one:

     

    Before:

     

    After:

     

    Here the code in Power Query M that you can paste into the advanced editor (if you do not know, how to exactly do this, please check out this quick walkthrough)

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyVtJRCijKTylNLlFwBLIdQUKxOtSTooowha4yMTVDknICsp1AQtilSNdBnhSpwrEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Code 1" = _t, Description = _t, #"Code 2" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Code 1", Int64.Type}, {"Description", type text}, {"Code 2", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Code 1", "Description"}, {{"MaxCode2", each List.Max([Code 2]), type nullable text}}),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"Code 1", "Description"}, #"Grouped Rows", {"Code 1", "Description"}, "Grouped Rows", JoinKind.LeftOuter),
    #"Expanded Grouped Rows" = Table.ExpandTableColumn(#"Merged Queries", "Grouped Rows", {"MaxCode2"}, {"Grouped Rows.MaxCode2"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Grouped Rows",{"Code 2"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Grouped Rows.MaxCode2", "Code 2"}})
in
    #"Renamed Columns"

     

    Let me know if this helps 🙂

     

    /Tom
    https://www.tackytech.blog/
    https://www.instagram.com/tackytechtom/