Problems with Evaluation of Dates

[Ekaterina_]

Hello everyone,

I get a wrong output of my power query which should count for how many days a process step has a specific status. Here is my sampel data:

Date of Export	Steps	Status
07.03.2024	Identify issue	In Preperation
07.03.2024	Communicate problem	TODO
07.03.2024	Define solution	TODO
07.03.2024	Adjust	TODO
08.03.2024	Identify issue	DONE
08.03.2024	Communicate problem	In Preperation
08.03.2024	Define solution	In Preperation
08.03.2024	Adjust	TODO
09.03.2024	Identify issue	DONE
09.03.2024	Communicate problem	DONE
09.03.2024	Define solution	In Preperation
09.03.2024	Adjust	TODO
10.03.2024	Identify issue	In Preperation
10.03.2024	Communicate problem	TODO
10.03.2024	Define solution	TODO
10.03.2024	Adjust	TODO

The output I get is this one:

  but this is not correct, correcf would be These values:

 

 Currebtly I am using Duration.TotalDays Max date of export - min date of export. I understand that this can't be cirrecht since the process is not linear.

I also thought about counting the rows woth the correponding status, but this doens't works since a export is not taken daily.

Is there any solution for this problem?

I would be very grateful for help.

Thank you in advance!

[Anonymous · Accepted solution]

Hi @Ekaterina_ ,

Please check if the following steps give you the expected output.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtc31jcyMDJR0lHyTEnNK8lMq1TILC4uTQUJ5CkEFKUWpBYllmTm5ynF6qAod87PzS3Ny0xOLElVKCjKT8pJzQWKhvi7+KOrdElNy8xLVSjOzykFG4RdlWNKVmlxCbKkBR63ufj7uaIrwu4iLL6wwOc2/OoxXWlJjCstCbkSm0qi3GaJz22GBqRFL7J6AvGLrBRPBCMrQ3NfLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Date of export" = _t, Steps = _t, Status = _t]),
    FilteredOutDelivered = Table.SelectRows(Source, each ([Status] <> "DONE")),

    ChangedType = Table.TransformColumnTypes(FilteredOutDelivered,{{"Date of export", type date}}, "sk-SK"),

   GroupedRows = Table.Group(ChangedType, {"Steps", "Status"}, {{"Days", each Table.RowCount(_)}}),

    PivotedColumn = Table.Pivot(GroupedRows, List.Distinct(GroupedRows[Status]), "Status", "Days")

in

    PivotedColumn

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum -- China Power BI User Group

[Ekaterina_]

Hello @Anonymous 

It works perfectly 🙂 

Thank you very much!

[Ekaterina_]

Here is the Power query I use:

let

    Source = Excel.CurrentWorkbook(){[Name="tblSteps"]}[Content],

    FilteredOutDelivered = Table.SelectRows(Source, each ([Status] <> "DONE")),

    ChangedType = Table.TransformColumnTypes(FilteredOutDelivered,{{"Date of export", type date}}, "sk-SK"),

   GroupedRows = Table.Group(ChangedType, {"Steps", "Status"}, {{"Days", each Duration.TotalDays(List.Max([Date of export]) - List.Min([Date of export])) +1, Int64.Type}}),

    PivotedColumn = Table.Pivot(GroupedRows, List.Distinct(GroupedRows[Status]), "Status", "Days")

in

    PivotedColumn