Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Lookup values in one column

Hi all,

 

I would like to see which products in a column are removed or added every month. Every month the same list of products is added to the column with an addition or subtraction from products. Here is an example of my data:

 

Product IDCreation file date
101-01-2022
201-01-2022
301-01-2022
101-02-2022
201-02-2022
301-02-2022
401-02-2022
101-03-2022
201-03-2022
401-03-2022

 

So in the table above, the added product is ID 4 in february. In march product ID 3 is removed.

I would like to visualize how many unique products are added and subtracted monthly. 

 

 

Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi Anonymous ,

     

    I think you don't need to transform your table in Powre Query. It would be memory intensive. I suggest you to create a DimDate table and create measure to achieve your goal.

    DimDate = CALENDARAUTO()

    Relationship:

    Measures:

    Measure = 
VAR _LIST = CALCULATETABLE(VALUES('Table'[Creation file date]),ALL('Table'))
RETURN
IF(MAX(DimDate[Date]) IN _LIST,CALCULATE(COUNT('Table'[Creation file date]))+0)
    Count of Add = 
VAR _ADD =
    ADDCOLUMNS (
        CALCULATETABLE ( VALUES ( 'Table'[Product ID] ), ALL ( 'Table' ) ),
        "StartDate",
            CALCULATE (
                MIN ( 'Table'[Creation file date] ),
                ALLEXCEPT ( 'Table', 'Table'[Product ID] )
            ),
        "EndDate",
            VAR _MaxEndDate =
                CALCULATE ( MAX ( 'Table'[Creation file date] ), ALL ( 'Table' ) )
            VAR _MaxDate =
                CALCULATE (
                    MAX ( 'Table'[Creation file date] ),
                    ALLEXCEPT ( 'Table', 'Table'[Product ID] )
                )
            RETURN
                IF ( _MaxDate = _MaxEndDate, BLANK (), EOMONTH ( _MaxDate, 0 ) + 1 )
    )
RETURN
    COUNTAX ( FILTER ( _ADD, [StartDate] = MAX ( DimDate[Date] ) ), [Product ID] )
    ID of Add = 
VAR _ADD =
    ADDCOLUMNS (
        CALCULATETABLE ( VALUES ( 'Table'[Product ID] ), ALL ( 'Table' ) ),
        "StartDate",
            CALCULATE (
                MIN ( 'Table'[Creation file date] ),
                ALLEXCEPT ( 'Table', 'Table'[Product ID] )
            ),
        "EndDate",
            VAR _MaxEndDate =
                CALCULATE ( MAX ( 'Table'[Creation file date] ), ALL ( 'Table' ) )
            VAR _MaxDate =
                CALCULATE (
                    MAX ( 'Table'[Creation file date] ),
                    ALLEXCEPT ( 'Table', 'Table'[Product ID] )
                )
            RETURN
                IF ( _MaxDate = _MaxEndDate, BLANK (), EOMONTH ( _MaxDate, 0 ) + 1 )
    )
RETURN
    CONCATENATEX (
        FILTER ( _ADD, [StartDate] = MAX ( DimDate[Date] ) ),
        [Product ID],
        " "
    )
    Count of Remove = 
VAR _ADD =
    ADDCOLUMNS (
       CALCULATETABLE( VALUES ( 'Table'[Product ID] ),ALL('Table')),
        "StartDate",
            CALCULATE (
                MIN ( 'Table'[Creation file date] ),
                ALLEXCEPT ( 'Table', 'Table'[Product ID] )
            ),
        "EndDate",
            VAR _MaxEndDate =
                CALCULATE ( MAX ( 'Table'[Creation file date] ), ALL ( 'Table' ) )
            VAR _MaxDate =
                CALCULATE (
                    MAX ( 'Table'[Creation file date] ),
                    ALLEXCEPT ( 'Table', 'Table'[Product ID] )
                )
            RETURN
                IF ( _MaxDate = _MaxEndDate, BLANK (), EOMONTH ( _MaxDate, 0 ) + 1 )
    )
RETURN
    COUNTAX(FILTER(_ADD,[EndDate] =MAX(DimDate[Date])),[Product ID])
    ID of Remove = 
VAR _ADD =
    ADDCOLUMNS (
        CALCULATETABLE ( VALUES ( 'Table'[Product ID] ), ALL ( 'Table' ) ),
        "StartDate",
            CALCULATE (
                MIN ( 'Table'[Creation file date] ),
                ALLEXCEPT ( 'Table', 'Table'[Product ID] )
            ),
        "EndDate",
            VAR _MaxEndDate =
                CALCULATE ( MAX ( 'Table'[Creation file date] ), ALL ( 'Table' ) )
            VAR _MaxDate =
                CALCULATE (
                    MAX ( 'Table'[Creation file date] ),
                    ALLEXCEPT ( 'Table', 'Table'[Product ID] )
                )
            RETURN
                IF ( _MaxDate = _MaxEndDate, BLANK (), EOMONTH ( _MaxDate, 0 ) + 1 )
    )
RETURN
    CONCATENATEX (
        FILTER ( _ADD, [EndDate] = MAX ( DimDate[Date] ) ),
        [Product ID],
        " "
    )

    Result is as below.

     

    Best Regards,
    Rico Zhou

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

     

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi Anonymous ,

     

    I think you don't need to transform your table in Powre Query. It would be memory intensive. I suggest you to create a DimDate table and create measure to achieve your goal.

    DimDate = CALENDARAUTO()

    Relationship:

    Measures:

    Measure = 
VAR _LIST = CALCULATETABLE(VALUES('Table'[Creation file date]),ALL('Table'))
RETURN
IF(MAX(DimDate[Date]) IN _LIST,CALCULATE(COUNT('Table'[Creation file date]))+0)
    Count of Add = 
VAR _ADD =
    ADDCOLUMNS (
        CALCULATETABLE ( VALUES ( 'Table'[Product ID] ), ALL ( 'Table' ) ),
        "StartDate",
            CALCULATE (
                MIN ( 'Table'[Creation file date] ),
                ALLEXCEPT ( 'Table', 'Table'[Product ID] )
            ),
        "EndDate",
            VAR _MaxEndDate =
                CALCULATE ( MAX ( 'Table'[Creation file date] ), ALL ( 'Table' ) )
            VAR _MaxDate =
                CALCULATE (
                    MAX ( 'Table'[Creation file date] ),
                    ALLEXCEPT ( 'Table', 'Table'[Product ID] )
                )
            RETURN
                IF ( _MaxDate = _MaxEndDate, BLANK (), EOMONTH ( _MaxDate, 0 ) + 1 )
    )
RETURN
    COUNTAX ( FILTER ( _ADD, [StartDate] = MAX ( DimDate[Date] ) ), [Product ID] )
    ID of Add = 
VAR _ADD =
    ADDCOLUMNS (
        CALCULATETABLE ( VALUES ( 'Table'[Product ID] ), ALL ( 'Table' ) ),
        "StartDate",
            CALCULATE (
                MIN ( 'Table'[Creation file date] ),
                ALLEXCEPT ( 'Table', 'Table'[Product ID] )
            ),
        "EndDate",
            VAR _MaxEndDate =
                CALCULATE ( MAX ( 'Table'[Creation file date] ), ALL ( 'Table' ) )
            VAR _MaxDate =
                CALCULATE (
                    MAX ( 'Table'[Creation file date] ),
                    ALLEXCEPT ( 'Table', 'Table'[Product ID] )
                )
            RETURN
                IF ( _MaxDate = _MaxEndDate, BLANK (), EOMONTH ( _MaxDate, 0 ) + 1 )
    )
RETURN
    CONCATENATEX (
        FILTER ( _ADD, [StartDate] = MAX ( DimDate[Date] ) ),
        [Product ID],
        " "
    )
    Count of Remove = 
VAR _ADD =
    ADDCOLUMNS (
       CALCULATETABLE( VALUES ( 'Table'[Product ID] ),ALL('Table')),
        "StartDate",
            CALCULATE (
                MIN ( 'Table'[Creation file date] ),
                ALLEXCEPT ( 'Table', 'Table'[Product ID] )
            ),
        "EndDate",
            VAR _MaxEndDate =
                CALCULATE ( MAX ( 'Table'[Creation file date] ), ALL ( 'Table' ) )
            VAR _MaxDate =
                CALCULATE (
                    MAX ( 'Table'[Creation file date] ),
                    ALLEXCEPT ( 'Table', 'Table'[Product ID] )
                )
            RETURN
                IF ( _MaxDate = _MaxEndDate, BLANK (), EOMONTH ( _MaxDate, 0 ) + 1 )
    )
RETURN
    COUNTAX(FILTER(_ADD,[EndDate] =MAX(DimDate[Date])),[Product ID])
    ID of Remove = 
VAR _ADD =
    ADDCOLUMNS (
        CALCULATETABLE ( VALUES ( 'Table'[Product ID] ), ALL ( 'Table' ) ),
        "StartDate",
            CALCULATE (
                MIN ( 'Table'[Creation file date] ),
                ALLEXCEPT ( 'Table', 'Table'[Product ID] )
            ),
        "EndDate",
            VAR _MaxEndDate =
                CALCULATE ( MAX ( 'Table'[Creation file date] ), ALL ( 'Table' ) )
            VAR _MaxDate =
                CALCULATE (
                    MAX ( 'Table'[Creation file date] ),
                    ALLEXCEPT ( 'Table', 'Table'[Product ID] )
                )
            RETURN
                IF ( _MaxDate = _MaxEndDate, BLANK (), EOMONTH ( _MaxDate, 0 ) + 1 )
    )
RETURN
    CONCATENATEX (
        FILTER ( _ADD, [EndDate] = MAX ( DimDate[Date] ) ),
        [Product ID],
        " "
    )

    Result is as below.

     

    Best Regards,
    Rico Zhou

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

     

  • rohit_singh's avatar
    rohit_singh
    Solution Sage
    4 years ago

    HI Anonymous ,

    You can create a pivot table in power query to help you perform this.


    Sample code
    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTIw1AUiIwMjI6VYnWglI0whY0whmEYjTI1GmBqRhEwwhWBmGWOaZYypESYUCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Product ID" = _t, #"Creation file date" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Product ID", Int64.Type}, {"Creation file date", type date}}),
    #"Duplicated Column" = Table.DuplicateColumn(#"Changed Type", "Creation file date", "Creation file date - Copy"),
    #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Duplicated Column", {{"Product ID", type text}}, "en-GB"), List.Distinct(Table.TransformColumnTypes(#"Duplicated Column", {{"Product ID", type text}}, "en-GB")[#"Product ID"]), "Product ID", "Creation file date - Copy", List.Count)
    in
    #"Pivoted Column"

    Pivot the products on the creation date and you will be able to see whether or not each product exists in a given month. Based on the values (0 or 1) you can created calculated columns to give you added and removed products.

    Hope this helps. Please mark this answer as the solution if it resolves your issue.

    Kind regards,

    Rohit

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Would this solution also work if I have more than 1000 products?

      • rohit_singh's avatar
        rohit_singh
        Solution Sage
        4 years ago

        It would work but I'm guessing it would be memory intensive. You could tweak this solution a little, for instance, you could pivot the date rather than the product. I'm assuming you have only month start dates so that should give you much fewer columns.
        You output would look like the transposed version of the above.