Power Query - Merge Error

[Anonymous]

This the is formula and PowerBI Merge tool that I am using.

However when I try to expand the generated column, I get this error.

[Anonymous]

Hi all, 

Thank you for suggestions so far.

So the issue is, that the custom function "Transform File (12), is creating an extra row of error specified above.

The strange thing is that it also generates a smaple Query Table that has 126 rows (as it should) with no error, but when I invoke the custom function with the exact same code, it gives the error. 

If I use use the errorless "Transform Sample Table" will I run into any issues down the line?

[mwegener]

Hi @Anonymous ,

can you post some information on how to perform your custom function?

[Anonymous]

This is the custom function being invoked.

The parameter 12 it refers to points to this binary file.

from which it extracts the following table from a sharepoint and promotes headers as such. 

This function works alone.

However when invoked in another table, it spits out that error.

I think it is something to do with the fact that the Table I'm invoking it inside of essentially repeats the same initial steps to drill down the source data to the required excel file.

The steps circled in red are already being performed by the function, I will delete them and report back.

[Anonymous]

So if I could remove source as the inital step in that table and just immediately invoke the function I believe this would solve the issue, unfortunately I cannot.

[mwegener]

Hi @Anonymous 

why you can't remove the step?

Could you post the Advance Editor M-Code?

[Anonymous]

let
Source = SharePoint.Files("www.sharepoint/xxxxxx", [ApiVersion = 15]),
#"Filtered Rows" = Table.SelectRows(Source, each Text.Contains([Folder Path], "Data/PTP/Global PTP Dashboard/Reference Files/Freight Vendor List")),
#"Filtered Hidden Files1" = Table.SelectRows(#"Filtered Rows", each [Attributes]?[Hidden]? <> true),
#"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File (12)", each #"Transform File (12)"([Content])),
#"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}),
#"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "Transform File (12)"}),
#"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File (12)", Table.ColumnNames(#"Transform File (12)"(#"Sample File (12)"))),
#"Changed Type" = Table.TransformColumnTypes(#"Expanded Table Column1",{{"Source.Name", type text}, {"Company Code", type text}, {"Vendor code", type text}, {"Vendor", type text}, {"Freight", type text}}),
#"Removed Source.Name" = Table.RemoveColumns(#"Changed Type",{"Source.Name", "Vendor"}),
#"Removed Duplicates" = Table.Distinct(#"Removed Source.Name")
in
#"Removed Duplicates"

Ahh yes, How do I remove what is in bold and allow the Invoke Customer Function Step to be the Initial step?

[v-lid-msft]

Hi @Anonymous ,

First of all, we suggest you to delete the url in your reply.

Sorry for that we can not remove the source step and keep them refresh dynamic at the same time. Does the tables that you used to merge with can refresh  normally?

Best regards,

[Anonymous · Accepted solution]

Thank you for point that out. 

What the issue ended up being, was that the Europe and LATAM excel files (which had not been loaded yet), were giving error due to incomplete data.

I simply filtered those files out while I am waiting for the data to be be populated.

[v-lid-msft]

Hi @Anonymous ,

Glad to hear that you have resolved your problem. Thank you for sharing this. If you have any other questions, please kindly ask here and we will try to resolve it.

Best regards,

[v-lid-msft]

Hi @Anonymous ,

Could you please check if the sheet you used is still exist in the origin excel file? Please also refer to the similar threads:

https://community.powerbi.com/t5/Desktop/Expression-Error-The-key-didn-t-match-any-rows-in-the-table/td-p/155986

https://community.powerbi.com/t5/Power-Query/Expression-Error-The-key-did-not-match-any-rows-in-the-table/td-p/9824/page/1

Best regards,

[mwegener]

Hi @Anonymous ,

i think the problem is somewhere else.

You are loading several Excel files "Query: Transform File (12)" and a sheet "Sheet2" does not meet your requirements.

The Power Query preview does not show the error, but the merge needs all the data to create the preview and shows the error in this step.

Regards,

Marcus

Dortmund - Germany
If I answered your question, please mark my post as solution, this will also help others.
Please give Kudos for support.