invoke custom function

[Tomek1982]

Hi, 

I try to invoke a custom function.

While digging into a case I found that this seems to be related to reading files from sharepoint.
There are 3 files

And a simple function:

which for some files is not working properly not finding file

[PwerQueryKees]

Apologies, I did not properly read you problem description.

I did so now...

What you are trying to do is very doabale and I have a lot of experience with processing sharepoint files.
It does need debugging.

What I normally do is to replace the "in" clause of the function that is giving trouble with the and intermediairy steps until I found the issues. This way you can see where it goes wrong and you can gradually develop your function.

Like

let 
   x = 1,
   y = 2,
   result = x + y
in
   X

As a first, I would like to see the output of the "Filtered Rows1" step.

Common cause for mismatches in filtering are:

• Case Sensitivity
  A <> a
  Does not seem to be the problem here.
• Extra spaces at the end or in the middle
  They are hard to spot, so can't tell if this could be your problem
• What you try to filter down is not what you expect it to be.
  A row that not exist in the first place will never show up after a filter 🙂

In adition I have some immediate questions:

how was the result below produced?

In the above result, if you click in the white space to the right of the word Table, What shows up at the bottom of the screen?

Why is there a "Filtered Rows1 step in here?

AND

I would take a slightly different approach (I don't have access to sharepoint at the moment, so there may be small mistakes):

• Take your list of filenames (like the screenshot with the survey temp column) 
• Add a column to construct the complete filename
• Make a separate query with the Sharepoint.Files result.
  Filer and massage it anyway you want.
  Make sur eyou get only files from the folder you want to take as input.
• Merge "List of Filenames" with "Sharepoint Files" on the filename column
• Expand the Content column (from the top of my head)
• Change your "Grab" function
  • to expect a binary parameter
  • remove the steps you showed in the screenshot
• Add a column to the result of the merge like below where 

= Table.AddColumn(#"Changed Type", "Survey result", each #"Survey result grab"([Sharepoint Files.Content]))​​

This wil also save you repeating calls to Sharepoint.Files() which can be very slow on large sharpoint sites.

Hope this helps...

[Tomek1982]

@PwerQueryKees 

Thanks for support. I am preetu sure it is a bug in a Power BI. I wrote the function to compare filename with calculated filename.

For some files it is working fine

and for some it is not

I copied both values from 2nd row to column in excel and I removed duplicates. Those cells are equal, and comparision is not working fine. On a screen there are different types, but it does not change anything.

[PwerQueryKees]

Hm. I find it highly unlikely this is a bug in powerquery. Comparisons are fundamental for the reliability of PowerQuery. I have not heard anyone else about this bug or encountred it myself.

My proffesor in college always said: "don't blame the compiler, debug your program."

The screenshots you send show something, but without seeing all the M code leading to it, I can not help you. In your situation I would start with trying to replicate the issue with local files (with Folder.File()). I could try this as well...

And try some of the tips I gave you earlier.

The quicker method is probably to use my suggestion to solve this with a merge though. This has other benefits as well.

[PwerQueryKees]

Here is your problem:

Change to:

= Table.AddColumn(#"Changed Type", "Survey result", each #"Survey result grab"([Survey result]))

[Tomek1982]

@PwerQueryKees  I do not think so [Survey temp] is a column with parameters to pass to the function. This part is correct. Anyway when I changed as you advised there was an error