Forum Discussion

EaglesTony's avatar
EaglesTony
Post Prodigy
1 year ago
Solved

How can I change this from DAX to PowerM Query

I have the following in DAX, however I need it in PowerM query and need help how do I convert it to that ?

 

PIBasedOnResolutionDate =
VAR _vtable =
FILTER (
CROSSJOIN (
SELECTCOLUMNS( 'Issues', "_ResolutionDate", 'Issues'[RESOLUTION_DATE] ), tblPIListing

),
[_ResolutionDate] >= tblPIListing[StartDate]
&& [_ResolutionDate] <= tblPIListing[EndDate]
)
RETURN
MAXX ( FILTER ( _vtable, [_ResolutionDate] = 'Issues'[RESOLUTION_DATE] ), [PI] )

Need Help
  • PwerQueryKees's avatar
    PwerQueryKees
    1 year ago

    From

    Using

    let
    Source = tblPILIsting,
    #"Added Custom" = Table.AddColumn(Source, "PI Date", each List.Dates([#"StartDate "],Duration.Days([#" EndDate"]-[#"StartDate "]),#duration(1,0,0,0))),
    #"Expanded PI Date" = Table.ExpandListColumn(#"Added Custom", "PI Date"),
    #"Merged Queries" = Table.NestedJoin(#"Expanded PI Date", {"PI Date"}, Issues, {"ResolutionDate"}, "Issues", JoinKind.RightOuter),
    #"Expanded Issues" = Table.ExpandTableColumn(#"Merged Queries", "Issues", {"Key ", "ResolutionDate"}, {"Key ", "ResolutionDate"})
in
    #"Expanded Issues"

    To



    Did I answer your question? Then please (also) mark my post as a solution and make it easier to find for others having a similar problem.
    Remember: You can mark multiple answers as a solution...
    If I helped you, please click on the Thumbs Up to give Kudos.

    Kees Stolker

    A big fan of Power Query and Excel

     

     

8 Replies

  • SundarRaj's avatar
    SundarRaj
    Super User
    1 year ago

    EaglesTony Can you possibly share the table that you are working with and what is it that you'd like to achieve in your ouput? Thanks

  • PwerQueryKees's avatar
    PwerQueryKees
    Super User
    1 year ago

    From

    Using

    let
    Source = tblPILIsting,
    #"Added Custom" = Table.AddColumn(Source, "PI Date", each List.Dates([#"StartDate "],Duration.Days([#" EndDate"]-[#"StartDate "]),#duration(1,0,0,0))),
    #"Expanded PI Date" = Table.ExpandListColumn(#"Added Custom", "PI Date"),
    #"Merged Queries" = Table.NestedJoin(#"Expanded PI Date", {"PI Date"}, Issues, {"ResolutionDate"}, "Issues", JoinKind.RightOuter),
    #"Expanded Issues" = Table.ExpandTableColumn(#"Merged Queries", "Issues", {"Key ", "ResolutionDate"}, {"Key ", "ResolutionDate"})
in
    #"Expanded Issues"

    To



    Did I answer your question? Then please (also) mark my post as a solution and make it easier to find for others having a similar problem.
    Remember: You can mark multiple answers as a solution...
    If I helped you, please click on the Thumbs Up to give Kudos.

    Kees Stolker

    A big fan of Power Query and Excel

     

     

    • EaglesTony's avatar
      EaglesTony
      Post Prodigy
      1 year ago

      I tried this, but got an Error on the 2nd step Added Custom, saying "Start Date' of the record wasn't found.

       

      I fixed it though as that command had spaces after some names.

       

      Now for this I am getting "The name "Issues" wasn't recoginzed:

      = Table.NestedJoin(#"Expanded PI Date", {"PI Date"}, Issues, {"ResolutionDate"}, "Issues", JoinKind.RightOuter)

  • EaglesTony's avatar
    EaglesTony
    Post Prodigy
    1 year ago

    I have 2 tables:

     

    tblPILIsting:

    PI                    StartDate     EndDate

    1                     1/1/2025      3/31/2025

    2                     4/1/2025      6/30/2025

     

    Issues

    Key              ResolutionDate

    1                  1/5/2025

    2                  4/1/2025

    3                  2/20/2025

     

    So what I need i another column to th Issues table

    Key              ResolutionDate    PI

    1                  1/5/2025              1

    2                  4/1/2025              2

    3                  2/20/2025            1

     

  • Omid_Motamedise's avatar
    Omid_Motamedise
    Super User
    1 year ago

    Try this one

     

    let
    // Load 'Issues' table
    IssuesTable = YourIssuesTable, 

    // Load 'tblPIListing' table
    PIListingTable = YourPIListingTable, 

    IssuesFiltered = Table.SelectColumns(IssuesTable, {"RESOLUTION_DATE"}),

    CrossJoined = Table.AddColumn(PIListingTable, "JoinedIssues", each IssuesFiltered),
    ExpandedCrossJoin = Table.ExpandTableColumn(CrossJoined, "JoinedIssues"),

    FilteredTable = Table.SelectRows(ExpandedCrossJoin, each 
        ([RESOLUTION_DATE] >= [StartDate] and [RESOLUTION_DATE] <= [EndDate])
    ),

    GroupedTable = Table.Group(FilteredTable, {"RESOLUTION_DATE"}, 
        {{"MaxPI", each List.Max([PI]), type number}}
    )

in
    GroupedTable
    • EaglesTony's avatar
      EaglesTony
      Post Prodigy
      1 year ago

      It gave me an error on this line:

      ExpandedCrossJoin = Table.ExpandTableColumn(CrossJoined, "JoinedIssues"),

       

      Error: 2 arguments were passed to a function which expects between 3 and 4

       

      I was able to get around it, but at the end it gives me only RESOLUTION_DATE and MaxPI, I still need  the Key for each

  • PwerQueryKees's avatar
    PwerQueryKees
    Super User
    1 year ago

    This was tested and worked. So the error comes from differences in the start data.

    • I used table names (or query names) from the table names you gave in your question. So Issues for the table with issues. Replace with your name if it is different in your queries.
    • For the Start Date, I see the column name has a a space at the end. Artefact of the copy paste. Check the column name in your own query and change the script accordingly. Just removing the final space will probably work.
  • v-priyankata's avatar
    v-priyankata
    Community Support
    1 year ago

    Hi EaglesTony 

    May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

    Thank you.