Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Syndicate_Admin
Administrator
Administrator

How to compare price day over day for different product

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

7 REPLIES 7
Syndicate_Admin
Administrator
Administrator

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

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

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

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

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!

Hi @Syndicate_Admin ,

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

vyanjiangmsft_0-1687140171854.png

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
Community Support
Community Support

Hi @Syndicate_Admin ,

According to your description, I create a sample.

vyanjiangmsft_0-1686896592056.png

Here's my solution.

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

vyanjiangmsft_1-1686896773208.png

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:

vyanjiangmsft_2-1686896900858.png

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.

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors