Lookup values in one column

[Anonymous]

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 ID	Creation file date
1	01-01-2022
2	01-01-2022
3	01-01-2022
1	01-02-2022
2	01-02-2022
3	01-02-2022
4	01-02-2022
1	01-03-2022
2	01-03-2022
4	01-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. 

[Anonymous · Accepted solution]

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]

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]

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

[rohit_singh]

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.