none
Excel Formula Convert to Power Query RRS feed

  • Question

  • Hello Guys,

    Please convert the below formula to power query.

    =IF([@[PMCM Received Date]]=MAX(IF(([Document Reference Number]=[@[Document Reference Number]])*([Package ID]=[@[Package ID]]),[PMCM Received Date])),"Latest","Superseded")

    I want to know the latest and superseded document based on Received Date taking into consideration the Package ID...

    i.e.

    1st Entry

    Document Ref#: Doc_123

    Received Date: 01-Oct-2017

    Package ID: PC1

    Status: Latest

    2nd Entry

    Document Ref#: Doc_123

    Received Date: 01-Oct-2017

    Package ID: PC2

    Status: Latest

    3rd Entry

    Document Ref#: Doc_123

    Received Date: 02-Oct-2017

    Package ID: PC1

    Status: Latest

    The last entry dated 02-Oct-2017 of Doc_123 will be the latest document and the first entry dated 01-Oct-2017 will be superseded.

    Any help is very much appreciated.

    Thanks,

    Mielkew





    • Edited by Mielkew Saturday, September 30, 2017 6:53 AM
    Saturday, September 30, 2017 6:50 AM

Answers

  • Hey Mielkew,

    Let me know if this is what you're looking for.

    BTW - Just as a heads up, the language of Power Pivot is DAX, but the language of Power Query is called M. Just so we can all be on the same page.

    • Marked as answer by Mielkew Thursday, October 5, 2017 9:05 AM
    Tuesday, October 3, 2017 6:46 PM

All replies

  • You can use Group By:

    let
        Source = Receipts,
        #"Grouped Rows" = Table.Group(Source, {"Document Ref#", "Package ID"}, {{"LastReceivedDate", each List.Max([Received Date]), type date}, {"AllRows", each _, Value.Type(Source)}}),
        #"Expanded AllRows" = Table.ExpandTableColumn(#"Grouped Rows", "AllRows", {"Received Date", "Status"}, {"Received Date", "Status"}),
        #"Added Custom" = Table.AddColumn(#"Expanded AllRows", "Latest Or Superseded", each if [Received Date] = [LastReceivedDate] then "Latest" else "Superseded")
    in
        #"Added Custom"

    Saturday, September 30, 2017 11:35 AM
  • Hi Marcel,

    I'm new with power query and to any DAX expression. I tried using the code by changing the "Document Ref#" to "Document Reference Number". unfortunately, I'm getting this expression error "Expression.Error: The import Receipts matches no exports. Did you miss a module reference?"

    let
        Source = Receipts,
        #"Grouped Rows" = Table.Group(Source, {"Document Reference Number", "Package ID"}, {{"LastReceivedDate", each List.Max([Received Date]), type date}, {"AllRows", each _, Value.Type(Source)}}),
        #"Expanded AllRows" = Table.ExpandTableColumn(#"Grouped Rows", "AllRows", {"Received Date", "Status"}, {"Received Date", "Status"}),
        #"Added Custom" = Table.AddColumn(#"Expanded AllRows", "Latest Or Superseded", each if [Received Date] = [LastReceivedDate] then "Latest" else "Superseded")
    in
        #"Added Custom"
    

    The excel formula above is the same column name in my power query.

    How to fix the error?

    Thanks,

    Mielkew

    Saturday, September 30, 2017 12:19 PM
  • "Receipts" is the name from your input table in Power Query.

    In my example, I created an additional query to load the table from Excel into Power Query.
    This query is loaded with "Connection Only".

    let
        Source = Excel.CurrentWorkbook(){[Name="Receipts"]}[Content],
        #"Changed Type" = Table.TransformColumnTypes(Source,{{"Document Ref#", type text}, {"Received Date", type date}, {"Package ID", type text}, {"Status", type text}})
    in
        #"Changed Type"

    Saturday, September 30, 2017 12:26 PM
  • Hi Marcerl,

    Power Query Data Source is from DropBox using API (I just Googled it). Please see link below for the register that I'm currently working on https://1drv.ms/x/s!AhwagWV1iyFQgnVTVRzEis-o_h1I

    What I'm trying to achieve here is to have a new Column R "Package Document Status" in IFC_All_Register Sheet. I manage to do it with a helper column. Since all the data are coming from DropBox, I don't want the user poking in the helper column (Column R), if it is possible I want the data to be automatically generated by adding a Custom Column in PowerQuery.

    I don't have much idea about DAX expression, I am a self-thought in PowerQuery. I can't find any related topic in Google that relates to my problem.

    Thanks,

    Mielkew

    Saturday, September 30, 2017 1:02 PM
  • Now it becomes completely unclear to me what is still left to be answered.

    Please note that the provided code is all Power Query.

    If you are looking for a DAX solution, then I won't be able to help you.

    Sunday, October 1, 2017 9:03 AM
  • Hey Mielkew,

    Let me know if this is what you're looking for.

    BTW - Just as a heads up, the language of Power Pivot is DAX, but the language of Power Query is called M. Just so we can all be on the same page.

    • Marked as answer by Mielkew Thursday, October 5, 2017 9:05 AM
    Tuesday, October 3, 2017 6:46 PM
  • Hi Miguel,

    First thanks for your response and YES this is exactly what I'm looking for. Also, thanks for pointing out the difference between DAX and M Language or Code (Whatever!!!), I'm also bit confused about it! hehehe

    I do always depend to Mr. Google!!! I do love to have a formal training or course but can't find one here in Qatar.

    Can you please provide all the steps to achieve this?

    Mielkew



    • Edited by Mielkew Thursday, October 5, 2017 9:33 AM
    Thursday, October 5, 2017 9:24 AM