Forum Discussion

ShawnPrice's avatar
ShawnPrice
Icon for Helper I rankHelper I
9 years ago
Solved

Show most recent information

Below is a memo field I have in our AX 2009 system and I need to be able only show the most current update which should be the top update for each sales person. When I add the memo field in BI I get ...
Need Help
  • Anonymous's avatar
    Anonymous
    9 years ago

    Hi ShawnPrice,

    Firstly, in query editor, add a custom column using the following formula.

    Table.AddColumn(Source,"splitcolumn", each Table.FromColumns({Text.Split([Memo], "BM")}))



    Secondly, expand your column in query editor and then add the following step in Advanced Editor.

     #"Split Custom.splitcolumn" = Table.SplitColumn(#"Expanded Custom.splitcolumn" ,"Custom.splitcolumn.Column1", Splitter.SplitTextByDelimiter("-"),2)


    Thirdly, trim the date column as shown in the following screenshot and change its type to Date.




    Apply these changes in Power BI Desktop, then create a column using the formula below and create a report as shown in the following screenshot.

    RankColumn = RANKX(FILTER(Table3,EARLIER(Table3[owner])=Table3[owner]),Table3[Custom.splitcolumn.Column1.1])


    For more details, please review attached PBIX file.

    Thanks,
    Lydia Zhang

Anonymous's avatar
Anonymous
Not applicable
9 years ago

Hi ShawnPrice,

Firstly, in query editor, add a custom column using the following formula.

Table.AddColumn(Source,"splitcolumn", each Table.FromColumns({Text.Split([Memo], "BM")}))



Secondly, expand your column in query editor and then add the following step in Advanced Editor.

 #"Split Custom.splitcolumn" = Table.SplitColumn(#"Expanded Custom.splitcolumn" ,"Custom.splitcolumn.Column1", Splitter.SplitTextByDelimiter("-"),2)


Thirdly, trim the date column as shown in the following screenshot and change its type to Date.




Apply these changes in Power BI Desktop, then create a column using the formula below and create a report as shown in the following screenshot.

RankColumn = RANKX(FILTER(Table3,EARLIER(Table3[owner])=Table3[owner]),Table3[Custom.splitcolumn.Column1.1])


For more details, please review attached PBIX file.

Thanks,
Lydia Zhang