Forum Discussion
komaragiri
11 months agoFrequent Visitor
Running Value for each column based on the previous column Value
My data source is EXCEL. read the excel data source and create a simple Dashboard in BI.
Test data attached. appreciate your help.
4 Replies
- lbendlinSuper User
- komaragiriFrequent Visitor
lbendlin Thank you for ur help.
- komaragiriFrequent Visitor
lbendlin Instead of DAX for dailyremaing calc, can I create that column during the Transformation step?
- lbendlinSuper User
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("TZA7DkMhDATvQo308A9MnxOkRRSvyA1yf8WQ2KHZYpZFA2Okx/1+pZyesALTzCPphf3CgmIImC0r78Koc23Nslt+C4pB6Zblh9kxIxxYHGMrB1bHBHrgkCE5T0MJmfNuAMdCe+PuEPKEdZlmIfXub0Swuwqxq7FTXF0T/414NWasa0cZTGbODw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t]), #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]), #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Date", type date}, {"R1", Int64.Type}, {"R2", Int64.Type}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", (k)=> let t = Table.SelectRows(#"Changed Type",each [Date]<=k[Date]) in 13749 - List.Sum(t[R1]) - List.Sum(t[R2]) ,Int64.Type) in #"Added Custom"How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done". Once you examined the code, replace the entire Source step with your own source.