Forum Discussion

Ekaterina_'s avatar
Ekaterina_
Icon for Helper I rankHelper I
2 years ago
Solved

Problems with Evaluation of Dates

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 StepsStatus
07.03.2024Identify 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!

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    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

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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_'s avatar
      Ekaterina_
      Icon for Helper I rankHelper I
      2 years ago

      Hello Anonymous 

      It works perfectly 🙂 

       

      Thank you very much!

  • Ekaterina_'s avatar
    Ekaterina_
    Icon for Helper I rankHelper I
    2 years ago

    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