How to compare price day over day for different product

[Syndicate_Admin]

Hello,

I have a large table, which contains thousands of products and its daily price (a few years). I want to create another table from the source table to show by product and its day over day price change.

My question is: how do I compare day over day record by different products using power query in Excel.

Thanks!

Dan

[Syndicate_Admin]

Do you mean at the custom column, I copy this in the added column fx = let..., I could not get any thing 😞

[AlienSx]

Hello, @Syndicate_Admin  (dhuang2005). If your question goes to me then I would like to see the structure of your source table and name of that table (name of your query). I need column names. Lets focus on those with prices, products and dates. I'll modify my code to your data structure. Then you will simply create blank query, copy my code and see how it works.  

[Syndicate_Admin]

Now it can run, however, with over a million record, adding this custom column seems taking forever to run. Previously, with over a million record, it took me a few seconds to run query like grouping and sum. Now it takes over an hour to run over 70K rows, so I can't see if it is working for large data. 😞

[AlienSx]

Hi, give this a try. Columns names are "product", "price" and "date". This code generates "change" column. Let me know if performance is acceptable. 

let
    source = your_last_step,
    f = (tbl as table) => 
        [a = List.Buffer(tbl[price]),
        count = List.Count(a),
        gen = 
            List.Generate(
                () => [i = 0, change = 0],
                (x) => x[i] < count,
                (x) => [i = x[i] + 1, change = a{i} / a{i - 1} - 1],
                (x) => x[change]
            ),
        out = Table.FromColumns(Table.ToColumns(tbl) & {gen}, Table.ColumnNames(tbl) & {"change"})][out],
    g = Table.Group(source, "product", {{"prices", each f(Table.Sort(_, "date"))}}),
    expand = Table.ExpandTableColumn(g, "prices", List.RemoveItems(Table.ColumnNames(source), {"product"}) & {"change"})
in
    expand

[Syndicate_Admin]

Hello,

When I populate the exact same data as yours (same column names), the custom column works. However, I have a larger table with more columns, and the name of the column is different. When I copy your formula and change the name of the columns accordingly for the Custom column formula you provided, I got error message:

How do I resolve this issue?

Thank you!

[v-yanjiang-msft]

Hi @Syndicate_Admin ，

"Changed Type" is the name of previous step when you add custom column. Change it based on your sample.

Best regards,

Community Support Team_yanjiang

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

[v-yanjiang-msft]

Hi @Syndicate_Admin ,

According to your description, I create a sample.

Here's my solution.

1.If you don't want to change the original table, create a duplicate.

2.Add a custom column.

if[Date]=List.Min(Table.SelectRows(#"Changed Type",(x)=>x[Product]=[Product])[Date]) then "" else let pre=Table.SelectRows(#"Changed Type",(x)=>x[Product]=[Product]and x[Date]=List.Max(Table.SelectRows(#"Changed Type",(x)=>x[Product]=[Product]and x[Date]<[Date])[Date]))[Price]{0}in([Price]-pre)/pre

Get the correct result:

I attach my sample below for your reference.

Best regards,

Community Support Team_yanjiang

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