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,
I have a set of data that are transactions, per product (Apples and pears) per type of transactions (Buy and sell) and a date of transaction.
I found this amazing video to do a running total in power query that was pretty easy to follow in the end. Really recommnend it:
https://www.youtube.com/watch?v=MCkLi49cc8Y
BUT!
The result only works for transaction lines that have all products and all types of transactions. I can't get a balance on products if it hasn't been sold that specific day for exemple. So I need to fill all days with all products and all types of transactions with 0$ to get a full transaction table.
Anyone has a recommendation for that?
Solved! Go to Solution.
Hello @Sibrulotte
i had some deep thoughts.. quite tricky to achieve. But I got a solution for you. Importan is that your starting source has for every row you need an example. So to get it work I had to add a New row with Pear and Sell with Amount null
let
Source = #table
(
{"Date","Product","TransType","Amount"},
{
{"43831","Apple","Buy","50"}, {"43831","Pear","Buy","100"}, {"43832","Apple","Sell","25"}, {"43834","Pear","Buy","60"}, {"43834","Pear","Sell",null}
}
),
ToDate = Table.TransformColumns
(
Source,
{
{
"Date",
each Date.From(Number.From(_))
}
}
),
ChangedType = Table.TransformColumnTypes(ToDate,{{"Amount", Int64.Type}}),
AddedDateMin = Table.AddColumn(ChangedType, "DateMin", each List.Min(ToDate[Date])),
AddedDateMax = Table.AddColumn(AddedDateMin, "DateMax", each List.Max(ToDate[Date])),
Group = Table.Group(AddedDateMax, {"Product", "TransType"}, {{"AllRows", each _, type table [Date=date, Product=text, TransType=text, Amount=number]}}),
Transform = Table.TransformColumns
(
Group,
{
{
"AllRows",
(tableint)=>
let
ListOfDatesToAdd = List.Difference(List.Dates(List.Min(tableint[DateMin]), Duration.TotalDays(List.Max(tableint[DateMax])-List.Min(tableint[DateMin]))+1,#duration(1,0,0,0)),tableint[Date]),
CreateRecords = List.Transform(ListOfDatesToAdd, (dateint)=> Record.AddField(Table.First(Table.RemoveColumns(tableint,{"Date","Amount", "DateMin","DateMax"})),"Date",dateint)),
Combine = Table.Combine({Table.FromRecords(CreateRecords), Table.RemoveColumns(tableint, {"DateMin", "DateMax"})})
in
Combine
}
}
),
RemoveOther = Table.SelectColumns(Transform,{"AllRows"}),
Expand = Table.ExpandTableColumn(RemoveOther, "AllRows", {"Product", "TransType", "Date", "Amount"}, {"Product", "TransType", "Date", "Amount"}),
ChangedType2 = Table.TransformColumnTypes(Expand,{{"Date", type date}, {"Amount", Int64.Type}}),
Sort = Table.Sort(ChangedType2,{{"Date", Order.Ascending},{"Product", Order.Ascending}, {"TransType", Order.Ascending}})
in
Sort
Copy paste this code to the advanced editor to see how the solution works. If this solution fits your need, copy and past a part of it and implement it in your query, or I could create a custom function what makes it easier to apply if you are not used that much to power query.
If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too
Have fun
Jimmy
Hello @Sibrulotte
could you please share the data example and your M-code to get a better understanding of the problem you have.
Please share also the expected outcome
All the best
Jimmy
Sensitive information on my data set so I'll make some up:
Transaction table:
Date,Product,TransType,Amount
2020-01-01,Apple,Buy,50
2020-01-01,Pear,Buy,100
2020-01-02,Apple,Sell,25
2020-01-04,Pear,Buy,60
After:
Date,Product,TransType,Amount
2020-01-01,Apple,Buy,50
2020-01-01,Apple,Sell,0
2020-01-01,Pear,Buy,100
2020-01-01,Pear,Sell,0
2020-01-02,Apple,Buy,0
2020-01-02,Apple,Sell,25
2020-01-02,Pear,Buy,0
2020-01-02,Pear,Sell,0
2020-01-03,Apple,Buy,0
2020-01-03,Apple,Sell,0
2020-01-03,Pear,Buy,0
2020-01-03,Pear,Sell,0
2020-01-04,Apple,Buy,0
2020-01-04,Apple,Sell,0
2020-01-04,Pear,Buy,60
2020-01-04,Pear,Sell,0
With that, I'll take the code I got from the youtube video to Get a balance each day for all my products per type of transaction.
Date,Product,TransType,Amount,Balance
2020-01-01,Apple,Buy,50,50
2020-01-01,Apple,Sell,0,0
2020-01-01,Pear,Buy,100,100
2020-01-01,Pear,Sell,0,0
2020-01-02,Apple,Buy,0,50
2020-01-02,Apple,Sell,25,25
2020-01-02,Pear,Buy,0,100
2020-01-02,Pear,Sell,0,0
2020-01-03,Apple,Buy,0,50
2020-01-03,Apple,Sell,0,25
2020-01-03,Pear,Buy,0,100
2020-01-03,Pear,Sell,0,0
2020-01-04,Apple,Buy,0,50
2020-01-04,Apple,Sell,0,25
2020-01-04,Pear,Buy,60,160
2020-01-04,Pear,Sell,0,0
And that's pretty much it.
Hello @Sibrulotte
have you been able to solve the problem with the replies given?
If so, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too
All the best
Jimmy
Hi,
so thank you very much for the effort put in this. I was not able to integrate the code you had provided. But I did find another way to achieve my goal quicker.
I created a table in my excel workbook with all possible combinations of fruits and transaction type.
Fruit;Type
Apple;Buy
Apple;Sell
Orange;Buy
Orange;Sell
I then added a custom column with two parametres that I retrieved through list queries.
List.Dates(Date_premier_fichier,Duration.Days(Date_dernier_fichier-Date_premier_fichier)+1,#duration(1,0,0,0))
Date_premier_fichier and Date_dernier_fichier are parametres that have been retrieved by doing a List query and using that to define the parameter:
vDatesDernier was obtained by running a query that had only the maxdate of all files and converting that into a list. (transform --> convert to list)
The result was a table with all possibilities of fruit, transaction types, and dates.
Success 🙂
Hi
If you create one more dataset, where you have the distinct values of the lines you want e.g.:
Pear, Sell,0
Pear, Buy,0
Apple, Sell,0
Apple, Buy,0
Then you create a column with a list of dates (from the first date you need until the last you need, maybee todays date and Expand that. Then you have a list with all buy/sell for your products. merge (left join) your current list onto the one just created (on date, product and TransType)
You can now have to fix the amount column, so that it is the number that you actually have or else just zero.
Hope it helps
Hello @Sibrulotte
I'm not watching any youtube video to try do understand somebody elses problem 🙂 sorry
but when I'm looking at your data, what you basically try to achieve is based on a transaction list, create a daily list including the current ballance of that product. Is that right?
If yes, i don't get logic for example this 3 lines
2020-01-02,Pear,Sell,0,0 => in the line before the ballance was 100
2020-01-03,Apple,Buy,0,50
2020-01-03,Apple,Sell,0,25 => how can that be when on the row above you had 50, why now is 25?
Jimmy
yeap,
so,
2020-01-02,Pear,Sell,0,0 => in the line before the ballance was 100 Not true, since there has not been any Sell transactions on Pears before.
2020-01-03,Apple,Buy,0,50
2020-01-03,Apple,Sell,0,25 => how can that be when on the row above you had 50, why now is 25? The only sell transaction on apples before that was for 25.
I think my expemple might have been easier to understand if we changed transaction type to say, region. So replace all "Buy" with "North America", and all "Sell" with "Europe" if you need a better grasp on my end result.
Hello @Sibrulotte
i had some deep thoughts.. quite tricky to achieve. But I got a solution for you. Importan is that your starting source has for every row you need an example. So to get it work I had to add a New row with Pear and Sell with Amount null
let
Source = #table
(
{"Date","Product","TransType","Amount"},
{
{"43831","Apple","Buy","50"}, {"43831","Pear","Buy","100"}, {"43832","Apple","Sell","25"}, {"43834","Pear","Buy","60"}, {"43834","Pear","Sell",null}
}
),
ToDate = Table.TransformColumns
(
Source,
{
{
"Date",
each Date.From(Number.From(_))
}
}
),
ChangedType = Table.TransformColumnTypes(ToDate,{{"Amount", Int64.Type}}),
AddedDateMin = Table.AddColumn(ChangedType, "DateMin", each List.Min(ToDate[Date])),
AddedDateMax = Table.AddColumn(AddedDateMin, "DateMax", each List.Max(ToDate[Date])),
Group = Table.Group(AddedDateMax, {"Product", "TransType"}, {{"AllRows", each _, type table [Date=date, Product=text, TransType=text, Amount=number]}}),
Transform = Table.TransformColumns
(
Group,
{
{
"AllRows",
(tableint)=>
let
ListOfDatesToAdd = List.Difference(List.Dates(List.Min(tableint[DateMin]), Duration.TotalDays(List.Max(tableint[DateMax])-List.Min(tableint[DateMin]))+1,#duration(1,0,0,0)),tableint[Date]),
CreateRecords = List.Transform(ListOfDatesToAdd, (dateint)=> Record.AddField(Table.First(Table.RemoveColumns(tableint,{"Date","Amount", "DateMin","DateMax"})),"Date",dateint)),
Combine = Table.Combine({Table.FromRecords(CreateRecords), Table.RemoveColumns(tableint, {"DateMin", "DateMax"})})
in
Combine
}
}
),
RemoveOther = Table.SelectColumns(Transform,{"AllRows"}),
Expand = Table.ExpandTableColumn(RemoveOther, "AllRows", {"Product", "TransType", "Date", "Amount"}, {"Product", "TransType", "Date", "Amount"}),
ChangedType2 = Table.TransformColumnTypes(Expand,{{"Date", type date}, {"Amount", Int64.Type}}),
Sort = Table.Sort(ChangedType2,{{"Date", Order.Ascending},{"Product", Order.Ascending}, {"TransType", Order.Ascending}})
in
Sort
Copy paste this code to the advanced editor to see how the solution works. If this solution fits your need, copy and past a part of it and implement it in your query, or I could create a custom function what makes it easier to apply if you are not used that much to power query.
If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too
Have fun
Jimmy
Hi @Sibrulotte,
If your dataset product column always has all of the possible products (just not on every day) you could pivot the Product column, replace nulls with 0 and then unpivot.
If I'm understanding you correctly I think this will give you the output you require for the next step.
(Thanks for the video link by the way, I haven't seen running totals done that simply before)
Regards,
Kim
Have I solved your problem? Please click Accept as Solution so I don't keep coming back to this post, oh yeah, others may find it useful also ;). |
If you found this post helpful, please give Kudos. It gives me a sense of instant gratification and, if you give me Kudos enough times, magical unicorns will appear on your screen. If you find my signature vaguely amusing, please give Kudos. | Proud to be a Super User! |
Hi, totally understand you on that you tube video. I puyt it up because it was actually super useful to me and hopefully to someone reading this later on that would need help.
So I'm NOT trying to get a balance of apples and pears (buy adds, sell substarcts).
I'm trying to get a balance of how many apples we're sold, how many apples we're bought, how many pears we're sold and how many pears we're bought.
Hello @Sibrulotte
okay, but nevertheless. Could you explain then the how the last number of my 3 lines posted are calculated?
Jimmy
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.