Hello @johnlhaase
help me.... didn't you ask me to separate months with min/max? Maybe I got you wrong. But this result you should achieve with my first code
and it's the 28.02, because this is the date when the on starts.
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 @johnlhaase - See if this works:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jdOxDYMwGEThXVwj2b7f4KTNAhkAsQL7l1iCQ1TJkyy78Nc93bqmmmtWUUlTij6P+9NbG88499c2/XGCLqBr0M3QLdB16F7QvaGrhUJQ5LufFEQxBV1MQRpTUMcUBDIFjUxBJlNQ6qICrUx5LfFa4rXEa4nXEq8luijRSYluKnineHTSL6rMpbAMLJ1zOwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Start of Month" = _t, EmpID = _t, Fleet = _t, OnOff = _t, #"Rotation Date" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Start of Month", type date}, {"Rotation Date", type date}}),
FakeNullRow =
{null} &
List.RemoveLastN(
Table.Column(
Source, "OnOff"
),
1
),
CombinedWithNullColumn = Table.ToColumns(#"Changed Type") & {FakeNullRow},
BackToTable = Table.FromColumns(CombinedWithNullColumn, Table.ColumnNames(#"Changed Type") & {"Previous OnOff Status"}),
#"Added Changed Status" = Table.AddColumn(BackToTable, "Changed Status", each if [Previous OnOff Status] = null then null else if [OnOff] = [Previous OnOff Status] then null else "Changed"),
#"Added Index" = Table.AddIndexColumn(#"Added Changed Status", "Index", 0, 1, Int64.Type),
#"Added Grouping" = Table.AddColumn(#"Added Index", "Grouping", each if [Changed Status] = "Changed" then [Index] else null),
#"Filled Down" = Table.FillDown(#"Added Grouping",{"Grouping"}),
#"Grouped Rows" = Table.Group(#"Filled Down", {"Grouping"}, {{"AllRows", each _, type table [Start of Month=nullable date, EmpID=nullable text, Fleet=nullable text, OnOff=nullable text, Rotation Date=nullable date, Previous OnOff Status=nullable text, Changed Status=nullable text, Index=number, Grouping=nullable number]}}),
#"Added Min Date" = Table.AddColumn(#"Grouped Rows", "Min Date", each Table.Min([AllRows], "Rotation Date")[Rotation Date]),
#"Added Max Date" = Table.AddColumn(#"Added Min Date", "Max Date", each Table.Max([AllRows], "Rotation Date")[Rotation Date]),
#"Expanded AllRows" = Table.ExpandTableColumn(#"Added Max Date", "AllRows", {"Start of Month", "EmpID", "Fleet", "OnOff", "Rotation Date"}, {"Start of Month", "EmpID", "Fleet", "OnOff", "Rotation Date"}),
#"Removed Other Columns" = Table.SelectColumns(#"Expanded AllRows",{"Start of Month", "EmpID", "Fleet", "OnOff", "Rotation Date", "Min Date", "Max Date"})
in
#"Removed Other Columns"
It turns this:
into this:
I did it by using some cool techniques from @ImkeF at this blog article, but instead of using a custom function, I just did it in the code.
- It takes your OnOff column and adds it to the end of the current table as a new column, but shifted by one row.
- Adds a "Changed" text field to see if there is a change from on to blank (off I presume.)
- Adds an index. I just needed a sequence of numbers.
- If it is "Changed" give me the number in the Index column, otherwise, give me a null
- I then filled this column down. Now at every change, I have a different number.
- Grouped by that number, then used Table.Min() and Table.Max() to extract the min/max dates, then re-expanded your original columns.
Everything is UI driven (or in the Add Custom Column box) except for three steps:
- FakeNullRow
- CombinedWithNullColumn
- BackToTable
You can see what each step is doing though by looking at it as you walk through it.
How to use M code provided in a blank query:
1) In Power Query, select New Source, then Blank Query
2) On the Home ribbon, select "Advanced Editor" button
3) Remove everything you see, then paste the M code I've given you in that box.
4) Press Done
5) See this article if you need help using this M code in your model.
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.
