locked
Parameter input as column header and compare its value RRS feed

  • Question

  • Hi,

     

    I have a table below which I need to compare the status (0 or 1) of two Product at a time. So after I pivotted the column Product, I get the Status value on each column of Product as header. Because I have a long list of Products in real situation so I created two Parameters name ProductA and ProductB then use it for filtering and then pivot and compare

     

     

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtc31TcyMDJQ0lEKKMrMK0ktArKMLcxNLI2ADEOlWB0caixMjcwszYEMA9xqLM1NjI3MsKlxTkzKSUW2CacKuD0YboGpwG2LU2IJ0CGVeO1BqMFtE0IN3C5M12QkFqUTCD2EGtyhh1CDbFcsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Product = _t, ID = _t, Status = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Product", type text}, {"ID", Int64.Type}, {"Status", Int64.Type}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each [Product] = ProductA or [Product] = ProductB),
    #"Pivoted Column" = Table.Pivot(#"Filtered Rows", List.Distinct(#"Filtered Rows"[Product]), "Product", "Status"),
    #"Added Conditional Column" = Table.AddColumn(#"Pivoted Column", "Compare", each if ProductA = ProductB then true else false)
    in
    #"Added Conditional Column"

     

    How to compare the two columns when you have parameters input as headers? 

    Wednesday, July 15, 2020 9:05 PM

Answers

  • Hi Marc,

    your logical test asks if the string "Cable" (content of your parameter ProductA) is equal to the string "Battery".

    If you want to reference the content of the column with the respective names, you'd have to put it into square brackets: [Cable] or [Battery]. But then, as you've recognized, it would be static.

    Actually, there is a different syntax to grab a field from the current row/record that allows dynamic column names when used in a Table.AddColumn-formula:

    Record.Field(_, ProductA)

    Where the underscore _ represents the current row/record. 

    Check out this code:

    let
        Source =
            Table.FromRows(
                Json.Document(
                    Binary.Decompress(
                        Binary.FromText(
                            "i45WMtc31TcyMDJQ0lEKKMrMK0ktArKMLcxNLI2ADEOlWB0caixMjcwszYEMA9xqLM1NjI3MsKlxTkzKSUW2CacKuD0YboGpwG2LU2IJ0CGVeO1BqMFtE0IN3C5M12QkFqUTCD2EGtyhh1CDbFcsAA==",
                            BinaryEncoding.Base64
                        ),
                        Compression.Deflate
                    )
                ),
                let
                    _t = ((type nullable text)
                    meta
                    [
                        Serialized.Text = true
                    ])
                in
                    type table [
                        Date = _t,
                        Product = _t,
                        ID = _t,
                        Status = _t
                    ]
            ),
        #"Changed Type" =
            Table.TransformColumnTypes(
                Source,
                {
                    {
                        "Date",
                        type date
                    },
                    {
                        "Product",
                        type text
                    },
                    {
                        "ID",
                        Int64.Type
                    },
                    {
                        "Status",
                        Int64.Type
                    }
                }
            ),
        ProductA = "Printer",
        ProductB = "Cable",
        #"Filtered Rows" =
            Table.SelectRows(
                #"Changed Type",
                each
                    [Product]
                    = ProductA
                    or [Product]
                    = ProductB
            ),
        #"Pivoted Column" =
            Table.Pivot(
                #"Filtered Rows",
                List.Distinct(#"Filtered Rows"[Product]),
                "Product",
                "Status"
            ),
        #"Added Conditional Column" =
            Table.AddColumn(
                #"Pivoted Column",
                "Compare",
                each
                    if
                        Record.Field(_, ProductA)
                        = Record.Field(_, ProductB)
                    then
                        true
                    else
                        false
            )
    in
        #"Added Conditional Column"


    Imke Feldmann - MVP Data Platform (PowerBI) - http://www.TheBIccountant.com 

    Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful - Thanks!

    • Marked as answer by marc_hll Monday, July 20, 2020 4:50 AM
    Sunday, July 19, 2020 8:22 AM

All replies

  • Thursday, July 16, 2020 2:59 AM
  • Thanks for reply but I think it does not solve my problem. I have tried out your Logical.From([Printer]*[Cable]), after that when I change the parameter to something else like Battery, it breaks! Although the new header name changes but the previous step Logical.From([Printer]*[Cable]) was still static. That was my issue
    Thursday, July 16, 2020 6:31 AM
  • Hi Marc,

    your logical test asks if the string "Cable" (content of your parameter ProductA) is equal to the string "Battery".

    If you want to reference the content of the column with the respective names, you'd have to put it into square brackets: [Cable] or [Battery]. But then, as you've recognized, it would be static.

    Actually, there is a different syntax to grab a field from the current row/record that allows dynamic column names when used in a Table.AddColumn-formula:

    Record.Field(_, ProductA)

    Where the underscore _ represents the current row/record. 

    Check out this code:

    let
        Source =
            Table.FromRows(
                Json.Document(
                    Binary.Decompress(
                        Binary.FromText(
                            "i45WMtc31TcyMDJQ0lEKKMrMK0ktArKMLcxNLI2ADEOlWB0caixMjcwszYEMA9xqLM1NjI3MsKlxTkzKSUW2CacKuD0YboGpwG2LU2IJ0CGVeO1BqMFtE0IN3C5M12QkFqUTCD2EGtyhh1CDbFcsAA==",
                            BinaryEncoding.Base64
                        ),
                        Compression.Deflate
                    )
                ),
                let
                    _t = ((type nullable text)
                    meta
                    [
                        Serialized.Text = true
                    ])
                in
                    type table [
                        Date = _t,
                        Product = _t,
                        ID = _t,
                        Status = _t
                    ]
            ),
        #"Changed Type" =
            Table.TransformColumnTypes(
                Source,
                {
                    {
                        "Date",
                        type date
                    },
                    {
                        "Product",
                        type text
                    },
                    {
                        "ID",
                        Int64.Type
                    },
                    {
                        "Status",
                        Int64.Type
                    }
                }
            ),
        ProductA = "Printer",
        ProductB = "Cable",
        #"Filtered Rows" =
            Table.SelectRows(
                #"Changed Type",
                each
                    [Product]
                    = ProductA
                    or [Product]
                    = ProductB
            ),
        #"Pivoted Column" =
            Table.Pivot(
                #"Filtered Rows",
                List.Distinct(#"Filtered Rows"[Product]),
                "Product",
                "Status"
            ),
        #"Added Conditional Column" =
            Table.AddColumn(
                #"Pivoted Column",
                "Compare",
                each
                    if
                        Record.Field(_, ProductA)
                        = Record.Field(_, ProductB)
                    then
                        true
                    else
                        false
            )
    in
        #"Added Conditional Column"


    Imke Feldmann - MVP Data Platform (PowerBI) - http://www.TheBIccountant.com 

    Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful - Thanks!

    • Marked as answer by marc_hll Monday, July 20, 2020 4:50 AM
    Sunday, July 19, 2020 8:22 AM
  • Thanks @Imke, it works!

    I have a follow-up question. Once I Close & Apply, how do I let other people to select the ProductA and ProductB. For me, I will always have to go into Transform Data/Editor to type in the parameter section for filtering but let's say if I publish this in the web service, is there a way other users can select their own ProductA and ProductB either by user-input field or dropdown list?


    • Edited by marc_hll Monday, July 20, 2020 5:15 AM
    Monday, July 20, 2020 5:13 AM