hi, I'm trying to create a Running Total of Flag that resets if there have been more than two hours where Flag = 0. The Table visual below loads data from Excel file where the 'Flag Running Total (Excel)' column has already been calculated. I would like to actually implement this with a Calculated Column in PowerBI instead, but have so far only managed an ordinary Running Total. Anyone able to help me (on my way)?
Many thanks, Bastiaan
@BastiaanBrak this is a rather interesting problem. I have worked with the data that you provided in the first screenshot and not the data that you provided through PBIX file.
This is how I worked it out. This problem is based around finding the Reset rows. The logic here is if a row has flag=1 is it prceeded by three zero s from the 3 previous rows? Please let me know if it works our for you.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ddJLCoAwDEXRvXRcMEntJ25F3P82RMQK0TtycEDJ9e17KouOxUQ95aSyiVzPdOQgimIo5Rb5yopSURpKRxkoTmKCoiiGUkIdfySmnhBLT4ihJ+A3VoJK0Ag6wSBwPJBPj4UdV+ZxZT+C539W5rgyjyv7eRsmUGxg2MD4/78NjhM=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Date = _t, #"Time " = _t, Flag = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Time ", type time}, {"Flag", Int64.Type}}),
#"Added Index3" = Table.AddIndexColumn(#"Changed Type", "Row#", 1, 1),
#"Changed Type2" = Table.TransformColumnTypes(#"Added Index3",{{"Row#", Int64.Type}}),
#"Added Index" = Table.AddIndexColumn(#"Changed Type2", "Index_3", 3, 1),
#"Added Index1" = Table.AddIndexColumn(#"Added Index", "Index_2", 2, 1),
#"Added Index2" = Table.AddIndexColumn(#"Added Index1", "Index_1", 1, 1),
#"Added Index5" = Table.AddIndexColumn(#"Added Index2", "Index_0", 0, 1),
#"Merged Queries" = Table.NestedJoin(#"Added Index5",{"Index_0"},#"Added Index5",{"Index_3"},"Added Index5",JoinKind.LeftOuter),
#"Expanded Added Index5" = Table.ExpandTableColumn(#"Merged Queries", "Added Index5", {"Flag"}, {"Flag.1"}),
#"Sorted Rows" = Table.Sort(#"Expanded Added Index5",{{"Row#", Order.Ascending}}),
#"Merged Queries1" = Table.NestedJoin(#"Sorted Rows",{"Index_0"},#"Sorted Rows",{"Index_2"},"Sorted Rows",JoinKind.LeftOuter),
#"Expanded Sorted Rows" = Table.ExpandTableColumn(#"Merged Queries1", "Sorted Rows", {"Flag"}, {"Flag.2"}),
#"Merged Queries2" = Table.NestedJoin(#"Expanded Sorted Rows",{"Index_0"},#"Expanded Sorted Rows",{"Index_1"},"Expanded Sorted Rows",JoinKind.LeftOuter),
#"Expanded Expanded Sorted Rows" = Table.ExpandTableColumn(#"Merged Queries2", "Expanded Sorted Rows", {"Flag"}, {"Flag.3"}),
#"Added Custom" = Table.AddColumn(#"Expanded Expanded Sorted Rows", "Reset_Rows", each Text.From([Flag.1])&Text.From([Flag.2])&Text.From([Flag.3])&Text.From([Flag])),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Index_3", "Index_2", "Index_1", "Index_0", "Flag.1", "Flag.2", "Flag.3"}),
#"Filtered Rows" = Table.SelectRows(#"Removed Columns", each ([Reset_Rows] = "0001")),
#"Added Index4" = Table.AddIndexColumn(#"Filtered Rows", "Reset_Index_1", 1, 1),
#"Added Index6" = Table.AddIndexColumn(#"Added Index4", "Reset_Index_0", 0, 1),
#"Merged Queries3" = Table.NestedJoin(#"Added Index6",{"Reset_Index_1"},#"Added Index6",{"Reset_Index_0"},"Added Index6",JoinKind.LeftOuter),
#"Expanded Added Index6" = Table.ExpandTableColumn(#"Merged Queries3", "Added Index6", {"Reset_Rows"}, {"Reset_Rows.1"}),
#"Added Custom1" = Table.AddColumn(#"Expanded Added Index6", "Reset_Row_End_Series", each if [Reset_Rows.1]<>null then ([Reset_Rows.1]-1) else Table.RowCount(#"Changed Type2")),
#"Added Custom2" = Table.AddColumn(#"Added Custom1", "Custom", each {[#"Row#"]..[Reset_Row_End_Series]}),
#"Expanded Custom" = Table.ExpandListColumn(#"Added Custom2", "Custom"),
#"Changed Type1" = Table.TransformColumnTypes(#"Expanded Custom",{{"Custom", Int64.Type}}),
#"Removed Other Columns" = Table.SelectColumns(#"Changed Type1",{"Reset_Index_1", "Custom"}),
#"Merged Queries4" = Table.NestedJoin(#"Removed Other Columns",{"Custom"},#"Changed Type2",{"Row#"},"Added Index3",JoinKind.LeftOuter),
#"Expanded Added Index3" = Table.ExpandTableColumn(#"Merged Queries4", "Added Index3", {"Date", "Time ", "Flag"}, {"Date", "Time ", "Flag"}),
#"Grouped Rows" = Table.Group(#"Expanded Added Index3", {"Reset_Index_1"}, {{"AD", each _, type table}}),
#"Added Custom3" = Table.AddColumn(#"Grouped Rows", "Custom", each let
Source=[AD],
#"Sorted Rows1" = Table.Sort(Source,{{"Custom", Order.Ascending}}),
Custom1 = List.Skip(List.Accumulate(#"Sorted Rows1"[Flag],{0},(state,current)=>state&{List.Last(state)+current})),
Custom2 = Table.FromColumns(Table.ToColumns(#"Sorted Rows1")&{Custom1}),
#"Renamed Columns" = Table.RenameColumns(Custom2,{{"Column1", "Reset_Index"}, {"Column2", "Row#"}, {"Column3", "Date"}, {"Column4", "Time"}, {"Column5", "Flag"}, {"Column6", "RNT"}}),
#"Added Custom3" = Table.AddColumn(#"Renamed Columns", "Desired_Running_Total", each if [Flag]=0 then 0 else [RNT]),
#"Removed Other Columns1" = Table.SelectColumns(#"Added Custom3",{"Row#", "Date", "Time", "Flag", "Desired_Running_Total"})
in
#"Removed Other Columns1"),
#"Removed Columns1" = Table.RemoveColumns(#"Added Custom3",{"AD"}),
#"Expanded Custom1" = Table.ExpandTableColumn(#"Removed Columns1", "Custom", {"Row#", "Date", "Time", "Flag", "Desired_Running_Total"}, {"Row#", "Date", "Time", "Flag", "Desired_Running_Total"}),
#"Merged Queries5" = Table.NestedJoin(#"Added Index3",{"Row#"},#"Expanded Custom1",{"Row#"},"Expanded Custom1",JoinKind.LeftAnti),
#"Removed Columns2" = Table.RemoveColumns(#"Merged Queries5",{"Expanded Custom1"}),
Custom1 = #"Removed Columns2",
Custom2 = List.Skip(List.Accumulate(#"Custom1"[Flag],{0},(state,current)=>state&{List.Last(state)+current})),
Custom3 = Table.FromColumns(Table.ToColumns(Custom1)&{Custom2}),
#"Renamed Columns" = Table.RenameColumns(Custom3,{{"Column1", "Date"}, {"Column2", "Time"}, {"Column3", "Flag"}, {"Column4", "Row#"}, {"Column5", "RNT"}}),
#"Added Custom4" = Table.AddColumn(#"Renamed Columns", "Custom", each if [Flag]=0 then 0 else [RNT]),
#"Renamed Columns1" = Table.RenameColumns(#"Added Custom4",{{"Custom", "Desired_Running_Total"}}),
#"Removed Columns3" = Table.RemoveColumns(#"Renamed Columns1",{"RNT"}),
Custom4 = #"Expanded Custom1"&#"Removed Columns3",
#"Sorted Rows1" = Table.Sort(Custom4,{{"Row#", Order.Ascending}}),
#"Removed Columns4" = Table.RemoveColumns(#"Sorted Rows1",{"Reset_Index_1"})
in
#"Removed Columns4"The reason why I did not work with the PBIX file data as it had duplication as following as oppose to the date time count =1 of your primary dataset.
Here's the final output before we get started here. I also attached the PBIX file.
That table is built with these measures ( some simple, but always favored in writing every measure explicitly)
Total Flag = SUM ( FactTable[Flag] )
Total RH = SUM ( FactTable[RH] )
Total Temp = SUM ( FactTable[Temp] )
Running Total =
CALCULATE(
[Total Flag],
FILTER(
ALL(FactTable),
MAX( FactTable[Index]) >= FactTable[Index]
&& Max(FactTable[Group]) = FactTable[Group]
&& FactTable[Index] <> 0
),
VALUES( FactTable[WS_Name])
)Not overly complex measures as that's the goal since it will run faster, easier to troubleshoot, etc. Just took some time in Power Query.
I'm not going to get into all the steps here that I did in Power Query since it will be better for you to step through and get an idea. The basic idea was to be able to create specific groups which will then be used for the cumulative figure. That is what the FactTable[Group] is referring to. In case you were curious, here's the PQ code ( it looks much worse than it is). But the cool thing about this is that it will work for as many WS_Names as you get..automatically. And you can toy around with how many 0 are needed to restart the count.
PQ code:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("rVzbqiW3Ef2Vw3ke2qoqXUp5mw+YYOJACMYPJjHEEGaCL4H8fapbl90lqWf3lgy5OATtVa2WVlWtVX2+//7904+ff/3ty+e3jz//8p8vv/z2/uH9u98///PH/314+/TjL//41xuED29oIMr/AfgnY+Rfbx8/yf8KfiP5L7ux/KfJ/4b83/QNhG/2ZW96kXn/4cNrmArSZch4gsQecgkRG8T98dwGJ0TqENeekRrEeCDaE6LtEGkJ0SpEys8YToiuQ7RLiE4h4vEe/QnPd3huCc83T2gbvNDh+SW8oPAOtM2d8LjDC0t4rPdz/4+NTnixw+MlvHhe7RMWb+cdBdNBxiVIMArTHvvJ6u7DgG/MGqgiD2+OBwWj7j8MKGeNcypJfptRbUI9cwD0tKOXTXNrAoXj/gsonkF75oElTP2gfDxh1O+0p561x6TzahcOsmsfs6cfWsK059XBHFvbHN2egewSpFOQmG+LesqehNwSpFeQdFwSVQ30LOSXAIM6PeF4OlY8iz0LhSVIVpA+PaPaVuxJiJcgY3NH9pPidc3TM1BcgqysWTDhwDxnFBzwj1kD1QQUjxd5Tik4YJ9b9PPpi4LkqxI2bBHJH7vpN7QEfD6+eGIifqWavYY/r2baQiR/EIGUmRGtOsi+B1/CVpGz1Jfko0uFH9tgo3ry0IGvPbgqV1kOM7IPRw24GUOez6Uncgd+q9q9BFeVKwtfEAVKl4qsd05d5tiB3yp8L8FVGRthc8Z7f2B7Hz2ovsJ02LeK4KfH7dvcWHjvLKdrsMk/Wnu+39CB6+Wz561cbnQQYwbnaNVxuzxtc9Aqd3t5bozWU4IO5CicSYY6cFoCV1lcqlY2ct78QXebIDOfwW0HbpfAVT7fyytjmeA9lbAQCc7b3tObWwL3zRuP5GLMlU+wAGd669nNL2GrJC8Fl0FOD84bgZVITtg9uYUlbJXtpfJib4FTS+aiC+685z238RK2SvuSz2xwmKDRexPOvNozW1yC1tk/xI0R4Tjp0h7KjltV7vbc1qy/gP/r7z/9esaPF63ajh/JHrwi11wCYFbt0wM/jno2+clXA6Bm7wEMHzWD4FuiiBf0Ggc0Iy/tVXjbwCMRhkw0kYwzqpzo4O0t+L/99M/PKgA0Fw26vAAMJNk8sU3AGO34BaAZtep09QK+GkJ7BoAz20lyteSHO1DxYR1fJyqWMwiAaXM2BPZ4Li6oD0GtnwuhPQZ7XZECsNH4i3RTA1jG1ztgN88lz5vNsYk8TjklgPUN0NcQZQOMMZjugYkIONYZSwC0HEDb7IPFYLLKIOwP4cwDoQvALgfQtv5S50F9BRSvMlAJwC0H0AoBjtFi3gGQgsucU0HsAvDLAegE7jZhP2/2dx234B17xcSmCyAsB8DNLfTsYkjilvVs6UK1LAHwcgA6l8tjW2mwXNoB9KDweyqMA/y//Pnjd29//+nLf3/+95RB5PcDEJQ6c88emgBWuPFQFYPSbG+5RK8DazkBD5HGK0fslln0OrD2jKB73luO0euwVr/g4+UGZQPcMo5eB3YNcDpb5+R6y0F6HdgPj9ZFO3FtJb0OHDTwu5ZWb/lJr4NqW8kkEXnI3V+xlV6H1e6Sza6A8lxMhzxwlyaYQ5tMeJzlCYtpAhka5HA8tNLP7xlNC0Rd/KbjJSgD+KbdNM/VxY7Jtap62bdcp3m2TtB2K63CGfqW+zTP1+Wps9emkuMtE2qes789n3D90Le8qHnSzjpcf75vGVLznF08G+rp5JYzNU/apRLfkYO2bnoiGxhU88ytvDhdAt3yqebJOzeA/RPfMqsWqLv03thD3/OsFsi71Pvcb/c98+oS+66H5TeM4I8ml6XX8DBhYU0EoWLgvd/xkGJAipZet7IWNqL0POA4Cc6HvwEuXPQe7U4c62F1K0oUlthGTFEER9Yqf+lyK9aCsE0Q5E11HRhRGWz9TqjVgJMxuOY+MkemLMSwRbrox0bew3QMvrmXAbiKQREDwEVvNjIhpoMIzcswxnOSQ0Ca8RDPakTvAIVXYviKPN6+DRftwc5AckWDpyfq+Hk5PlEE7igjVaU3ckmzNmRbW+haGTmW2zAfh2oXZD+IH2J1jM5a5UF3ccQ/Ko5GNXcbCWEm1oLNR4w4difHqvlSJJrE/WbR5FcDGwTQzDXQjuEPi0QzudscGEjUIS8H0dM5kl5E1uuXIlGB+M0Rx2Qdy5ZE8nwuL3oxGf6oOGxzeyWtB5P5XG4vhDGhDzVle6UnPmXRNLSAwt4+Hhpu2Ahi9hdOwDAk0eSrzTq5CZu24G3KIlLaOaRksJ2uKA7J81h9ZWo9dXITtrRn4JKXIS/A7zJqedbT7eyd3GQnzTq5aUpF+mKwlSeN1DK2gJeLaBW45qdpL7dMJslG57xptsAh+VhwunxOoev17mV4PRiFG5hoDGROtBiJM3y5cl7Day1leirsuC4sN4wL/O4kg/Pl0JVbF96vC8nXjXRo4H0A79N1CybSIYdcX7dm+es+up4Ms1K0+lQwSlvj5Z8vblwc9ROvu+jq1UUpkIJQbtaV0RlX0Js7FweNBFxNSF2j6+Ct3DrjYupjjBQqVJiuuXRxIOXDFdU9nyBI6G4DoHTnozR1Ns9q9ZeuGSD4eCoUZwYICtMyZuc2bi4ylxPXXLlmfCCBX7HdNbhrNh4ktWZwqYFMKOjNjYsDaR+u6O4a3TePLtte6Q7JEZRTV9oFVvA6w13R3TV8m+OsNCmlFrUsLUuBL9VGVPBqOVzR3TW8SlQsG85ZjvQR+Jj9TMpooRvdGajV8Prcjk50bh+CzQMDsDlpnqEGUPkOVADqB2CC8HSyg73afDSJBKf6plAeNJynfmFidKjJOHLobIihZByLXK9+3YOG99QP4OvEp5MGbp6lA8rjmSrh1y3Q1Kf7w9epr0255E1SjQTfkVRb5R0U3gdNfvr5Xyc/JTdIweP3cWiXzgCZQKarcccMlPBf5z+lNLDfBBFDLvfYp9Eptf1jBkrwrxOgEhn2alNuXigMFKX5Cu32jyko4b/OgEp5jvsjC34mYLOPZ5eCryReHJNQwn+dApUCHf2j4NzTjzW+vP2SeXFMQQl+YnbRNPjRMFHefyOdBpYASvbFCwZKEUyQIDQRkLM57+yjwvS4ACUDY8NAI4VocnquFAExoMWciwCttW0axkRCXd+fWq4rFrzT8JcSzAFSLsEkGgptJkanI1ABXNHgnfm50msHds4kLzhEx77wcE3H6FUEegtm5jj1xxlS/Ek+DOkusJyK4ysBlY8xqAjUerqiwjtqR4lAHhzy8BLv3UCNoLYgrCJQ6+mKDe+M0JVvREKQmiDPbxlmU2uSwocYVQRqPV3x4Z0ZuqI9WJNtS6nJuLDhwxnXeo9aTFd0eGeCrsBLCk6qZJR06F2or6Cqb6AiUOvpihFvC8WSktCEQsnSgCfl/oigym6oItDyyxUj3paIpSaIHrMyK/sR2VKpBx6GOakQ1A/MTfMqXt+1AGnADx1WboXkCF934f2sBXTicBL+pghRV4ZSGaGNmK6CNEbBl5Og5YBOFE4RwKSFojUJKdF31TF97hmcZKow3Ibhx2qzIWhaxA2d9SHdCAD5xzjch+Ena7NGkuZFu39hEIhybpCTUYrEsR6pWXHR1Ct3wrD3PhdqsCtF5TiMZUm13C6aeiUGdtKo5uEdK81CvNBJRnq0W/T0StdqbOQy4uyNq9QwFij1MN9kCNxug88KqWFEKnswlii1CzcZQGyOY/Q2xmxKCFeHGkOjmowkap51201zLa0gF602SNaq+kWjnQyV6ie+5n3B2kXMCs7+AQLHWrl2KspQsp72/ds2minUEQjA9Bma4knNUnoCYpUpi5gRmYQf07kQ1qbaTdVmVhOVWj/tuquWUG6oQ4SQdSXgXVb+as5Qy2GWLlVjKEUMSxSYr4hE89A1x1lDLYdZvmyKCL95oYrD75dTYZBcLajHiUOvh1nKbCoJabKj9wHKufBkyukc5w69Hp7Q5m2Dw+0CG3LWmna5vzocY51dr4cn7HnT6RD6tJ6T07F7ukJjNZ2PFXdNGE/486bjIVGgZVdIXDruWIIYC+96K57Q503jY6+0pbIx2Wb3+we7JYqx/q6LqyfcedMA2U1PoAd5OuJ6PscyvFqOT8jzphOyJ7RqBsjp9PHhgF0J8uoHcHpgyDdxGHrMpURhDfNMl1c/gE8Y9KY5svcf5jTCFZirN3OlzusZgCcketcloY12SzyzF4OcjqoQV41UK2TqB/AJi973SwhdrrrkrBh38ksKjTZSvfoBnKfR1jbhOrgkm2OEzWp7XHi00ez1L+A8kbbV1y4V2rwlxO7knhQqhYZL1S/gNJl2Po43mL77220MpFMkhU8bH6ExcqYZVQdihTko6xW4z3Kl74BV9aMpVX+HO02puhR0e4vIeac2Sbqx+gr17mpO1V/jTnOqbcKwIH1BcZfQuzpSUeU8Tan6m9xpSnVNGDuluvwXCuQSY+xcDs2o+svcaUb1zdmI0rqGfF28KX804f3kdmhC1d/nThNqaMIA6d1D3g3rHFCpSqvnoflUf6U7zae6T4hyRC2FnOc47H834l1XQKjpVH+rO02nbaewk2nMu2Eku9i2mUZNpvqT3XkubXuFYOWUZgZDaaihc0AaKlU/QPNUCs2GAACb3L8Jp5Op6aU6IQ2Vjv6Ywdo8ai3VpSLFXKrH4C3VkazqiWhHQv+CfcKntw0qCcXt8xJF+In7LGgJpbaU2prQU7pPKPW2UyWRULDu8Ej2SCB/Za8Kosal0HvyhFXvO1a0m+emuMdBXtVDfal+idbJ1Q/YJ8R637kiOSgElGXBSP6kA1XfROvl6gfsE26972DBLpNX84JjuUrvp5qoMS+0VPuEXm87WXsk9Q9ymP3Pb3Htcx82irZ11Q/YJwx729TaI4HHmFf9QOiIpH6JqO1d9QMr89xtnWgC14YXQIi/m/cZ3pxkqjzh2Nt/LIXNY+JwP69SwVdiG7s7arldYFhdGe1NP2OZb7fyYsg1+zE+q+lz2gV2bcdwiMBiTjqI+Ji9LfsxPqkpjgVu1bWRk/2IvkwEepLqvR2DHZ/TFMcCs+q5nN13szHPAjg+zQIUXtWzCGq1W6BVXRzFfUIvHQmW8t1ClYcKqeqBBLXaLXBqL6Six2M0iyX3WuLyVgqlNkWAWu4WKLUrjnZtBvMfwZC9sW3N2pQA+qN7YdQf/g8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [WS_Name = _t, Date = _t, Time = _t, RH = _t, Temp = _t, Flag = _t, #"Flag=1 RT + Reset (Excel)" = _t, #"Flag=0 RT + Reset (Excel)" = _t, #"Flag final RT + Reset (Excel)" = _t, DateTime = _t, #"Flag final RT + Reset (PowerBI)" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"WS_Name", type text}, {"Date", type date}, {"Time", type time}, {"RH", type number}, {"Temp", type number}, {"Flag", Int64.Type}, {"Flag=1 RT + Reset (Excel)", Int64.Type}, {"Flag=0 RT + Reset (Excel)", Int64.Type}, {"Flag final RT + Reset (Excel)", Int64.Type}, {"DateTime", type text}, {"Flag final RT + Reset (PowerBI)", Int64.Type}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Flag=1 RT + Reset (Excel)", "Flag=0 RT + Reset (Excel)", "Flag final RT + Reset (Excel)"}),
#"Inserted Merged Date and Time" = Table.AddColumn(#"Removed Columns", "Date Time", each [Date] & [Time], type datetime),
#"Reordered Columns" = Table.ReorderColumns(#"Inserted Merged Date and Time",{"WS_Name", "Date", "Time", "Date Time", "RH", "Temp", "Flag", "DateTime", "Flag final RT + Reset (PowerBI)"}),
#"Removed Columns1" = Table.RemoveColumns(#"Reordered Columns",{"DateTime", "Flag final RT + Reset (PowerBI)"}),
#"Grouped Rows" = Table.Group(#"Removed Columns1", {"WS_Name"}, {{"Data", each _, type table}}),
#"Sort Date Time" = Table.AddColumn(#"Grouped Rows", "SortDateTimeAsc", each Table.Sort( [Data], {"Date Time", Order.Ascending})),
#"Add Index Column from 1" = Table.AddColumn(#"Sort Date Time", "IndexFrom1", each Table.AddIndexColumn( [SortDateTimeAsc], "Index", 1, 1 )),
#"Add Index Column from 0" = Table.AddColumn(#"Add Index Column from 1", "IndexFrom0", each Table.AddIndexColumn( [IndexFrom1], "Index2", 0, 1 )),
#"Select Columns" = Table.AddColumn(#"Add Index Column from 0", "SelectColumns", each Table.SelectColumns(
[IndexFrom0], {"WS_Name", "Date Time", "RH", "Temp","Flag", "Index", "Index2"} )),
#"Removed Other Columns" = Table.SelectColumns(#"Select Columns",{"SelectColumns"}),
#"Filter on Flag <> 0" = Table.AddColumn(#"Removed Other Columns", "FilterFlag", each Table.SelectRows( [SelectColumns], each [Flag] <> 0 )),
#"Index from 1 again" = Table.AddColumn(#"Filter on Flag <> 0", "Index1", each Table.AddIndexColumn( [FilterFlag], "Filtered Index", 1, 1 )),
#"Index from 0 again" = Table.AddColumn(#"Index from 1 again", "Index2", each Table.AddIndexColumn( [Index1], "Filtered Index2", 0, 1 )),
#"Merge to get Prev Row" = Table.AddColumn(#"Index from 0 again", "MergePrevRow2", each Table.NestedJoin([Index2],{"Filtered Index2"},[Index2],{"Filtered Index"},"Prev Row",JoinKind.LeftOuter)),
#"Expand Prev Index" = Table.AddColumn(#"Merge to get Prev Row", "Expand", each Table.ExpandTableColumn(
[MergePrevRow2],
"Prev Row", {"Index"}, {"Prev Index"})),
#"Add Compare Column" = Table.AddColumn(#"Expand Prev Index", "AddCompare", each Table.AddColumn(
[Expand], "Compare",
each
if
[Prev Index] = null then [Index]
else
[Prev Index] +1
)),
#"Removed Other Columns1" = Table.SelectColumns(#"Add Compare Column",{"AddCompare"}),
#"Select Columns 2" = Table.AddColumn(#"Removed Other Columns1", "SelectColumns", each Table.SelectColumns(
[AddCompare],
{"WS_Name", "Date Time", "RH", "Temp", "Flag", "Index", "Compare"} )),
#"Add Index Column" = Table.AddColumn(#"Select Columns 2", "Index3", each Table.AddIndexColumn(
[SelectColumns], "Index3",
1,1)),
#"NewCount or InOrder Column" = Table.AddColumn(#"Add Index Column", "NewCount", each Table.AddColumn(
[Index3],
"Order",
each
if
[Index3] = 1 then "New Count"
else
if
[Index] <= ( [Compare] + StartCount)
then
"In Order"
else
"New Count"
)),
#"Removed Other Columns2" = Table.SelectColumns(#"NewCount or InOrder Column",{"NewCount"}),
#"Sort Columns" = Table.AddColumn(#"Removed Other Columns2", "Sort", each Table.Sort( [NewCount],
{{"Order", Order.Descending}, {"Index", Order.Ascending}})),
#"Another Index" = Table.AddColumn(#"Sort Columns", "Index4", each Table.AddIndexColumn(
[Sort],
"Index4",
1,1)),
#"Column to add index to count" = Table.AddColumn(#"Another Index", "Count and Index", each Table.AddColumn(
[Index4],
"Count",
each
if
[Order] = "New Count"
then
[Order]&" "& Text.From( [Index4] )
else
null
)),
#"Sort By Index" = Table.AddColumn(#"Column to add index to count", "SortByIndex", each Table.Sort( [Count and Index], {{"Index", Order.Ascending}})),
#"Split Column" = Table.AddColumn(#"Sort By Index", "Split Column", each Table.SplitColumn([SortByIndex], "Count", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, true), {"Count", "Group"})),
FillDown = Table.AddColumn(#"Split Column", "FillDown", each Table.FillDown([Split Column],{"Group"})),
#"Extract Time" = Table.AddColumn(FillDown, "Extract Time", each Table.AddColumn([FillDown], "Time", each DateTime.Time([Date Time]), type time)),
#"Extract Date" = Table.AddColumn(#"Extract Time", "Extract Date", each Table.TransformColumns([Extract Time],{{"Date Time", DateTime.Date, type date}})),
#"Select Columns 3" = Table.AddColumn(#"Extract Date", "SelectColumns3", each Table.SelectColumns(
[Extract Date],
{"WS_Name", "Date Time","Time", "RH", "Temp", "Flag","Group"})),
#"Add Index Column 3" = Table.AddColumn(#"Select Columns 3", "NewIndex", each Table.AddIndexColumn(
[SelectColumns3],
"Index",
1,1 )),
#"Removed Other Columns3" = Table.SelectColumns(#"Add Index Column 3",{"NewIndex"}),
#"Expanded NewIndex" = Table.ExpandTableColumn(#"Removed Other Columns3", "NewIndex", {"WS_Name", "Date Time", "Time", "RH", "Temp", "Flag", "Group", "Index"}, {"WS_Name", "Date Time", "Time", "RH", "Temp", "Flag", "Group", "Index"}),
#"Set Data Types" = Table.TransformColumnTypes(#"Expanded NewIndex",{{"WS_Name", type text}, {"Date Time", type date}, {"Time", type time}, {"RH", type number}, {"Temp", type number}, {"Flag", Int64.Type}, {"Index", Int64.Type}, {"Group", Int64.Type}}),
#"Renamed Columns" = Table.RenameColumns(#"Set Data Types",{{"Date Time", "Date"}}),
#"Appended with Null Table" = Table.Combine({#"Renamed Columns", #"Null Table"}),
#"Sorted Rows" = Table.Sort(#"Appended with Null Table",{{"Date", Order.Ascending}, {"Time", Order.Ascending}})
in
#"Sorted Rows"One thing I did do was to separate the Date Time column into one Date and one Time Column. It is better to have this split if you can since it will allow for better compression by the Vertipaq engine. I think I have rambled enough here. Let me know what you think!
Here's the file:
hi @Anonymous , @Greg_Deckler
Thanks for your interest in my question! Flag is a column (I calculate this at the Powerquery stage in my actual data model) but for simplicity I have included it in Excel input file in this case. Below is a link to download a mock PowerBI report that contains a simplified dataset for two locations, screengrab of download link for transparency.
https://filebin.net/gnx5ezeqhqlm4hy1
Hope this is clear now but happy to clarify further if needed.
Regards, Bastiaan
Can you take a look at the below screen shot and see if this is what you had in mind? There's a lot I did in Power Query (much of it which was just trying something) and need to clean it up, but i think/hope this is what you had i mind. This was no easy task...
-Nick
hi @Anonymous , many thanks for your work on this. Based on your screenshot it's not yet entirely as I need it but it seems you've cracked the core part of resetting the Running Total when Total Flag = 0. However, I want the Running Total to reset ONLY if more than 2 hours had a 'Total Flag' value of zero; so Running Total at 19 March 12:00 PM turns into 13 rather than 1, etc. (see screengrab below).
Note: the screenshot in my first post inaccurately gives the Running Total for 19 March 5:00 PM as 17, this should be 16.
@BastiaanBrak , ah I see now. I had it set up to compare if the next row was within 1. Let me mess around with it and see if I can fix that, I think it should be a relatively easy fix ( which naturally means it wont be 
)
Is Flag a measure or column? Can you post that data as text? Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!: The Definitive Guide to Power Query (M)
DAX is easy, CALCULATE makes DAX hard...
Helpful resources
Microsoft Fabric Learn Together
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Power BI Monthly Update - April 2024
Check out the April 2024 Power BI update to learn about new features.
Fabric Community Update - April 2024
Find out what's new and trending in the Fabric Community.
