Forum Discussion

Kaitra's avatar
Kaitra
Frequent Visitor
4 years ago
Solved

Power Query needed formula

I have the following situation.

 

Column AColumn B
AAA 
AAA 
CCC 
BBB 
DDD 
CCC 
CCCdeactive
DDDdeactive

 

In Column A are different values. In column B one value "deactive" is set. I am searching for a formula that now putting the value in Column C "deactive" in every row were Column A is null (see table below as a solution).

 

Column AColumn BColumn C
AAA  
AAA  
CCC deactive
BBB  
DDD deactive
CCC deactive
CCCdeactivedeactive
DDDdeactivedeactive

 

I hope someone can help me out 🙂

 

Cheers,

Kai

Need Help
  • BA_Pete's avatar
    BA_Pete
    4 years ago

    Kaitra ,

     

    Ah yes, I see.

     

    In Power Query, go to New Source>Blank Query then in Advanced Editor paste my code over the default code. You can then follow the steps I took to complete this:

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnR0VNJRUlCK1UFlOzs7w9lOTk5wtouLC1Y1EHZKamJySWZZKpJShFAsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Column A" = _t, #"Column B" = _t]),
    chgTypes = Table.TransformColumnTypes(Source,{{"Column A", type text}, {"Column B", type text}}),
    groupColumnA = Table.Group(chgTypes, {"Column A"}, {{"data", each _, type table [Column A=nullable text, Column B=nullable text]}}),
    addMaxColumnB = Table.AddColumn(groupColumnA, "maxColumnB", each Table.Max([data], "Column B")),
    expandMaxColumnB = Table.ExpandRecordColumn(addMaxColumnB, "maxColumnB", {"Column B"}, {"Column B"}),
    expandData = Table.ExpandTableColumn(expandMaxColumnB, "data", {"Column B"}, {"Column B.1"})
in
    expandData

     

    SUMMARY:

    1) Group table on [Column A] and keep All Rows as [data]

    2) Get max value of column B from nested table and expand record

    3) Expand [data] again to reinstate original data

     

    This gives me the following output:

     

    Pete

  • AlexisOlson's avatar
    AlexisOlson
    4 years ago

    I had something written before I saw BA_Pete's answer. Please accept that one if it works for you but I thought I might as well post a slightly different alternative since it's already written.

     

    You could group by Column A and define the max over Column B as Column C and then merge this back to the original table.

     

    Group:

     

    Merge and Expand:

     

    Full sample M code you can paste into your Advanced Editor:

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnR0VNJRUlCK1UFlOzs7w9lOTk5wtouLC1Y1EHZKamJySWZZKpJShFAsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Column A" = _t, #"Column B" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column A", type text}, {"Column B", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Column A"}, {{"Column C", each List.Max([Column B]), type nullable text}}),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"Column A"}, #"Grouped Rows", {"Column A"}, "Grouped Rows", JoinKind.LeftOuter),
    #"Expanded Grouped Rows" = Table.ExpandTableColumn(#"Merged Queries", "Grouped Rows", {"Column C"}, {"Column C"})
in
    #"Expanded Grouped Rows"

     

8 Replies

  • AlexisOlson's avatar
    AlexisOlson
    Super User
    4 years ago

    I had something written before I saw BA_Pete's answer. Please accept that one if it works for you but I thought I might as well post a slightly different alternative since it's already written.

     

    You could group by Column A and define the max over Column B as Column C and then merge this back to the original table.

     

    Group:

     

    Merge and Expand:

     

    Full sample M code you can paste into your Advanced Editor:

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnR0VNJRUlCK1UFlOzs7w9lOTk5wtouLC1Y1EHZKamJySWZZKpJShFAsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Column A" = _t, #"Column B" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column A", type text}, {"Column B", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Column A"}, {{"Column C", each List.Max([Column B]), type nullable text}}),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"Column A"}, #"Grouped Rows", {"Column A"}, "Grouped Rows", JoinKind.LeftOuter),
    #"Expanded Grouped Rows" = Table.ExpandTableColumn(#"Merged Queries", "Grouped Rows", {"Column C"}, {"Column C"})
in
    #"Expanded Grouped Rows"

     

  • BA_Pete's avatar
    BA_Pete
    Super User
    4 years ago

    Hi @ anyone who might be interested.

     

    Just for fun, I combined my solution and AlexisOlson 's solution into a single step.

    The following should be added as a custom step:

     

    Table.Group(
    previousStep,
    {"Column A"},
    {
        {"Column C", each try
        Text.Split(
            Text.Trim(
                Text.Repeat(
                    List.Max([Column B]) & " ",
                    Table.RowCount(_)
                )
            ), " "
        ) otherwise null}
    }
)

     

     

    Expand the resulting list and, Voila!

     

    Full M code:

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnR0VNJRUlCK1YGxwUxnZ2e4sJOTE5zt4uICZyOrgbBTUhOTSzLLUpGUIoRiAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Column A" = _t, #"Column B" = _t]),
    repBlankNull = Table.ReplaceValue(Source," ",null,Replacer.ReplaceValue,{"Column B"}),
    groupColumnA =
    Table.Group(
        repBlankNull,
        {"Column A"},
        {
            {"Column C", each try
            Text.Split(
                Text.Trim(
                    Text.Repeat(
                        List.Max([Column B]) & " ",
                        Table.RowCount(_)
                    )
                ), " "
            ) otherwise null}
        }
    ),
    expandColumnC = Table.ExpandListColumn(groupColumnA, "Column C")
in
    expandColumnC

     

    Pete

    • AlexisOlson's avatar
      AlexisOlson
      Super User
      4 years ago

      This is a fun combo. 🙂

       

      Not ideal if there are additional columns in the query though.

      • Kaitra's avatar
        Kaitra
        Frequent Visitor
        4 years ago

        Thanks a lot both of you. I really appreciate the quick and helpful support here in the forum!!!

        Like AlexisOlson mentioned, I had additional columns which I needed (forget to mention in the topic). So the solution from both of you is working pretty well 🙂

  • BA_Pete's avatar
    BA_Pete
    Super User
    4 years ago

    Hi Kaitra ,

     

    I'm not sure I understand: you have values in column C where column A isn't null.

    Have I misunderstood your requirement?

     

    Pete

    • Kaitra's avatar
      Kaitra
      Frequent Visitor
      4 years ago

      Hi BA_Pete 

       

      the source table is the first one. I'd like to have column C as a result. Column C doesn't exist in the current table. I needs to be created. So the situation is, that in Column B a value is set only for one entry, I'd like to have it for every matching entry. In my example it would be "CCC" and "DDD".

      • BA_Pete's avatar
        BA_Pete
        Super User
        4 years ago

        Kaitra ,

         

        Ah yes, I see.

         

        In Power Query, go to New Source>Blank Query then in Advanced Editor paste my code over the default code. You can then follow the steps I took to complete this:

        let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnR0VNJRUlCK1UFlOzs7w9lOTk5wtouLC1Y1EHZKamJySWZZKpJShFAsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Column A" = _t, #"Column B" = _t]),
    chgTypes = Table.TransformColumnTypes(Source,{{"Column A", type text}, {"Column B", type text}}),
    groupColumnA = Table.Group(chgTypes, {"Column A"}, {{"data", each _, type table [Column A=nullable text, Column B=nullable text]}}),
    addMaxColumnB = Table.AddColumn(groupColumnA, "maxColumnB", each Table.Max([data], "Column B")),
    expandMaxColumnB = Table.ExpandRecordColumn(addMaxColumnB, "maxColumnB", {"Column B"}, {"Column B"}),
    expandData = Table.ExpandTableColumn(expandMaxColumnB, "data", {"Column B"}, {"Column B.1"})
in
    expandData

         

        SUMMARY:

        1) Group table on [Column A] and keep All Rows as [data]

        2) Get max value of column B from nested table and expand record

        3) Expand [data] again to reinstate original data

         

        This gives me the following output:

         

        Pete