Forum Discussion

ishade's avatar
ishade
Regular Visitor
10 months ago
Solved

Syntax for TOPN on Source Statement of Power Query

Hi, I would like to setup a report to show data of a 'Event' table of Salesforce (Alexis). 

I tried to prepare a DataFlow but got empty table but columns and got advise from raja1992 of the possible reason. (related link : Empty Columns (no data) of DataFlow of a Salesforc... - Microsoft Fabric Community

 

So I tried to put TOPN option on source statement. Would anyone kindly advise me of how to convert the statement

    

             ソース{[Name = "Event"]}[Data]
 
with putting TOPN so that I can limit the volume to retrieve. ('ソース' means 'source' in Japanese)
 

 

 
Tips and Tricks
  • Irwan's avatar
    Irwan
    10 months ago

    hello ishade 

     

    i am not sure but seems the last step is not correct.

     

    the M code should put the previous step

    you previous step is Navigation (sorry i dont write it in japanese)

     

    or else, you can use the ribbon button so you dont have to type the code manually (the selection from ribbon should automatically detect the previous step).

     

    Thank you.

8 Replies

  • Irwan's avatar
    Irwan
    Super User
    10 months ago

    hello ishade 

     

    TOPN is a DAX.

    While in your screenshot, it looks like PQ so you need M instead of DAX.

     

    in PQ, you can create an index then filter row above certain value of that index.

     

     

    Hope this will help.

    Thank you.

    • ishade's avatar
      ishade
      Regular Visitor
      10 months ago

      Hi, Irwan

       

      Thanks for your kind reply. I tried to insert step on my Power Query as following; 

       

      but got error of 'Token Litheral expected'. 

      The steps in screenshot are 1. source, 2. navigation, 3. custom (at which I applied "= Table.AddIndexColumn(#"Changed Type", "Index", 1, 1, Int64.Type)"). 

       

      I guess main cause of this would be no table at step 2, navigation (on which timeout error happend after 10 minutes.) 

      • Irwan's avatar
        Irwan
        Super User
        10 months ago

        hello ishade 

         

        i think that because it should be dot instead of comma after Int64

         

        Thank you.

         

  • Praful_Potphode's avatar
    Praful_Potphode
    Super User
    10 months ago

    Based on the screenshot,you will have to expand your data and convert it into a table.

    Then You can try below step in power query

     

    the number highlighted shows the number of records you want from the table