Forum Discussion

komaragiri's avatar
komaragiri
Frequent Visitor
1 year ago
Solved

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.     
komaragiri's avatar
komaragiri
Frequent Visitor
1 year ago

lbendlin Instead of DAX for dailyremaing calc, can I create that column during the Transformation step? 

lbendlin's avatar
lbendlin
Icon for Super User rankSuper User
1 year ago
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.