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...

Need Help

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.

Lookup values in one column.pbix25 KB

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

Not applicable

4 years ago

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

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.

Forum Discussion

Lookup values in one column

Related content

lookup value in another column

Lookup rank value?

Lookup value

Splitting Column values

Lookup help