Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi everyone, I really need your help to create a new substraction column in power query
editor. I am new to power bi and never use M language before.
This is the the photo of the data that i have. I want to get the price differences between two same book but different year. lets say " price difference between "History2 2013" and " History2 2014" is $8.
For the book that does not have previous version of it the price difference should be 0.
Here is my disired result:
The formula should look up every single row that meet certain condition such as same book and genre, but (Year-1)
Difference: [Price]-[Price from previous year from the same item]
Thank you so much!!
Solved! Go to Solution.
Copying DAX from this post? Click here for a hack to quickly replace it with your own table names
Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C
I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com
######EDITED############
try using this code power query:
yourTab = Source{[Item="Foglio1",Kind="Sheet"]}[Data],
breakTab=(tab as table) =>
let
tabSorted=Table.Sort(tab,{"Price"}),
breakList=List.Accumulate(List.Numbers(1,Table.RowCount(tabSorted)-1), {},(s,c)=>s&{tabSorted[Price]{c}-tabSorted[Price]{c-1}})
breakTab=(tab as table) =>
let
tabSorted=Table.Sort(tab,{"Year"}),
breakList=List.Accumulate(List.Numbers(0,Table.RowCount(tabSorted)), {},(s,c)=>s&{try (tabSorted[Price]{c}-tabSorted[Price]{c-1}) otherwise 0})
in
Table.FromColumns({tab[Year],tab[Price],breakList},{"Year","Price","difference"}),
#"Promoted Headers" = Table.PromoteHeaders(yourTab, [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Book", type text}, {"Genre", type text}, {"Year", Int64.Type}, {"Price", Int64.Type}, {"Difference", Int64.Type}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"Book", "Genre"}, {{"diff", each breakTab(_)}}),
#"Expanded diff" = Table.ExpandTableColumn(#"Grouped Rows", "diff", {"Year", "Price", "difference"}, {"Year", "Price", "difference"})
in
#"Expanded diff"
could get somethoink like this:
Copying DAX from this post? Click here for a hack to quickly replace it with your own table names
Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C
I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com
Copying DAX from this post? Click here for a hack to quickly replace it with your own table names
Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C
I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com
Hi, can you please provide a solution on power editor as i have the same issue with big data on directquery mode. if possible to share the pbix file as well, it will be the best. thanks for your support.
I really need your suggestion with this problem, considering that i will run a big data set and i need this calculation take less memory and run fast when i pull up a new data set . whats your recommendation?
"considering that you will run a big data set and you need this calculation take less memory and run fast",
consider to try this version which could be more performing.
how many rows and column your data set has?
let
...
#"Promoted Headers" = Table.PromoteHeaders(yourTab, [PromoteAllScalars=true]),
#"Sorted Rows" = Table.Sort(#"Promoted Headers",{{"Book", Order.Ascending}, {"Genre", Order.Ascending}, {"Year", Order.Ascending}}),
breakTab=(tab as table) =>
let
diffPrice=List.Transform(List.Numbers(0,Table.RowCount(tab)), each try (tab[Price]{_}-tab[Price]{_-1}) otherwise 0)
in
Table.FromColumns({tab[Year],tab[Price],diffPrice},{"Year","Price","difference"}),
#"Grouped Rows" = Table.Group(#"Sorted Rows", {"Book", "Genre"}, {{"diff", each breakTab(_)}},GroupKind.Local),
#"Expanded diff" = Table.ExpandTableColumn(#"Grouped Rows", "diff", {"Year", "Price", "difference"}, {"Year", "Price", "difference"})
in
#"Expanded diff"
Copying DAX from this post? Click here for a hack to quickly replace it with your own table names
Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C
I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com
It would be amazing if you could show me how to make a measure to solve that problem Thank you so much
@bryanrendra I have updated this with ALL() option for complete solution.
See if this makes sense, all as new MEASURES (you can mark it also as solution if it works).
Note: I have split them into smaller measures to make it easier to read and so that you can use the Previous and Latest Price measures on their own in case that is something you want to analyze:
LatestPrice = MAXX(FILTER(BookPrices, BookPrices[Year]=MAX(BookPrices[Year])),BookPrices[Price])
PreviousPrice =
VAR LatestYear = MAXX(BookPrices,BookPrices[Year])
RETURN MAXX(FILTER(BookPrices, BookPrices[Year]<LatestYear),BookPrices[Price])
-- This PreviousPrice works only when YEAR is not in visual or filter.
PriceDifference = IF(ISBLANK([PreviousPrice]), 0, [LatestPrice]-[PreviousPrice])
Copying DAX from this post? Click here for a hack to quickly replace it with your own table names
Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C
I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com
Hi Allison, i think its a incredible approach. However when i tried to calculate the previous price, it doesnt work. Do you mind to check the formula one more time ? i think we need to find the year right before. If its a python/ java might be easier. get_value_Table[price] where table[year]-1. something like that. your help is wonderful!!
Copying DAX from this post? Click here for a hack to quickly replace it with your own table names
Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C
I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com
Here is the result what i got, and still get nothing. Considering also that there is a certain year that the book sold out so there will be no price / "0"
Copying DAX from this post? Click here for a hack to quickly replace it with your own table names
Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C
I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com
I dont want to remove the year table since its needed. do you mind to elaborate what you mean by using all()?
Copying DAX from this post? Click here for a hack to quickly replace it with your own table names
Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C
I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com
this is the answer that i have been looking for,
Thank you so much Allison, you have been really helpfull. I really appreciate it 🙂
######EDITED############
try using this code power query:
yourTab = Source{[Item="Foglio1",Kind="Sheet"]}[Data],
breakTab=(tab as table) =>
let
tabSorted=Table.Sort(tab,{"Price"}),
breakList=List.Accumulate(List.Numbers(1,Table.RowCount(tabSorted)-1), {},(s,c)=>s&{tabSorted[Price]{c}-tabSorted[Price]{c-1}})
breakTab=(tab as table) =>
let
tabSorted=Table.Sort(tab,{"Year"}),
breakList=List.Accumulate(List.Numbers(0,Table.RowCount(tabSorted)), {},(s,c)=>s&{try (tabSorted[Price]{c}-tabSorted[Price]{c-1}) otherwise 0})
in
Table.FromColumns({tab[Year],tab[Price],breakList},{"Year","Price","difference"}),
#"Promoted Headers" = Table.PromoteHeaders(yourTab, [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Book", type text}, {"Genre", type text}, {"Year", Int64.Type}, {"Price", Int64.Type}, {"Difference", Int64.Type}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"Book", "Genre"}, {{"diff", each breakTab(_)}}),
#"Expanded diff" = Table.ExpandTableColumn(#"Grouped Rows", "diff", {"Year", "Price", "difference"}, {"Year", "Price", "difference"})
in
#"Expanded diff"
could get somethoink like this:
Hi, can you please share the pbix file. thanks for your support.
Hi, i see trhis is can solve my issue as well but i am not expert in power bi so i appreciate your support to provide me by the pbix file so i can understand more what steps you did so i can follow. thanks for your support.
Hi, thank you so much for the alternative way that you have given 🙂 its helpful!!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.