Syntax for TOPN on Source Statement of Power Query

[ishade]

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)

[Praful_Potphode]

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

[danextian]

hi @ishade 

TOPN is a DAX function. You can use Table.FirstN to select the first number of rows. You may also use Table.AddRankColumn to create a rank column then filter the generated rank - this function works similarly with DAX's RANKX. 

[Irwan]

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]

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]

hello @ishade 

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

Thank you.

[ishade]

Hi Irwan

Thanks for your kind correction. But I am sorry to report you that it still shows some syntax error as following ;

[Irwan · Accepted solution]

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.

[ishade]

Hi Irwan

Thanks for your kind comment to put Index with right name of previous step.

But unfortunately, I did not make it since the 3rd. step does not reconignize the 2nd. one.,

which would means I should focus on 2nd. step (yes, navigation) including access right.

Anyway, closing this shred for a while, 

I would like to appreciate your valuable time to support this matter. Have a nice day!!!