Remove Errors - Doesn't?

[Martinl]

Hi all

I have a query that grabs a load of excel files off of a sharepoint folder and merges them together.
Once open if I look through the data set in the PowerQuery builder I can see at the bottom of the dataset that there is a complete row of ERRORs in every column.
So I thought I'd just use the Remove Errors feature - nothing happens

Even If I just bring in one file with just 3 rows in the Excel work sheet the same thing occurs

Any ideas

One thing strikes me in the code mat be a red herring........When I invoke the procedure the path changes to
http://sp13 emea xxxxx net/emea/EMDBS/Sales/ without the "." from
http://sp13.emea.xxxxx.net/emea/EMDBS/Sales/

this is my code

let
    Source = SharePoint.Files("http://sp13.emea.xxxxx.net/emea/EMDBS/Sales/", [ApiVersion = 14]),
    #"Lowercased Text" = Table.TransformColumns(Source,{{"Extension", Text.Lower, type text}}),
    #"Filtered Rows1" = Table.SelectRows(#"Lowercased Text", each ([Extension] = ".xlsx")),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Filtered Rows1", "Folder Path", Splitter.SplitTextByDelimiter("/", QuoteStyle.Csv), {"Folder Path.1", "Folder Path.2", "Folder Path.3", "Folder Path.4", "Folder Path.5", "Folder Path.6", "Folder Path.7", "Folder Path.8", "Folder Path.9", "Folder Path.10", "Folder Path.11", "Folder Path.12", "Folder Path.13"}),
    #"Filtered Rows" = Table.SelectRows(#"Split Column by Delimiter", each ([Folder Path.7] = "Channel Programme") and ([Folder Path.9] <> "" and [Folder Path.9] <> "Source Sell-Out reports")),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Extension", "Date accessed", "Date modified", "Date created", "Attributes", "Folder Path.1", "Folder Path.2", "Folder Path.3", "Folder Path.4", "Folder Path.5", "Folder Path.6", "Folder Path.7", "Folder Path.8"}),
    #"Uppercased Text" = Table.TransformColumns(#"Removed Columns",{{"Folder Path.12", Text.Upper, type text}}),
    #"Replaced Value" = Table.ReplaceValue(#"Uppercased Text"," ","",Replacer.ReplaceText,{"Folder Path.12"}),
    #"Filtered Rows2" = Table.SelectRows(#"Replaced Value", each ([Folder Path.12] <> "")),
    #"Invoke Custom Function1" = Table.AddColumn(#"Filtered Rows2", "Transform File from http://sp13 emea xxxxx net/emea/EMDBS/Sales/", each #"Transform File from http://sp13 emea xxxxx net/emea/EMDBS/Sales/"([Content])),
    #"Removed Other Columns1" = Table.SelectColumns(#"Invoke Custom Function1",{"Folder Path.9", "Folder Path.10", "Folder Path.11", "Folder Path.12", "Transform File from http://sp13 emea xxxxx net/emea/EMDBS/Sales/"}),
    #"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File from http://sp13 emea xxxxx net/emea/EMDBS/Sales/", Table.ColumnNames(#"Transform File from http://sp13 emea  xxxxx net/emea/EMDBS/Sales/"(#"Sample File"))),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded Table Column1",{{"Purchase Date", type date}}),
    #"Removed Columns1" = Table.RemoveColumns(#"Changed Type",{"Column13", "Column14", "Column15", "Column16", "Column17", "Column18", "Column19", "Column20", "Column21", "Column22", "Chemical", "Column24", "1-10 Employees"}),
    #"Filtered Rows3" = Table.SelectRows(#"Removed Columns1", each ([Qty] <> null)),
    #"Changed Type1" = Table.TransformColumnTypes(#"Filtered Rows3",{{"Column8", type date}, {"RC Number", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type1", "Custom", each Text.End("0000000000"&[RC Number], 10)),
    #"Reordered Columns" = Table.ReorderColumns(#"Added Custom",{"Folder Path.9", "Folder Path.10", "Folder Path.11", "Folder Path.12", "Purchase Date", "Partner name", "RC Number", "Custom", "SKU", "Material Name - Property -", "Model", "Qty", "Column8", "Sold to (Customer Name)", "Customer type (select)", "Application", "Customer size (select)"}),
    #"Removed Columns2" = Table.RemoveColumns(#"Reordered Columns",{"RC Number"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns2",{{"Custom", "RC Number"}})
in
    #"Renamed Columns"

 

[Ken Puls]

Your issue is mostly likely with the order you are doing things here:

[COLOR=#333333]    #"Changed Type" = Table.TransformColumnTypes(#"Expanded Table Column1",{{"Purchase Date", type date}}),
[/COLOR]    #"Removed Columns1" = Table.RemoveColumns(#"Changed Type",{"Column13", "Column14", "Column15", "Column16", "Column17", "Column18", "Column19", "Column20", "Column21", "Column22", "Chemical", "Column24", "1-10 Employees"}),
    #"Filtered Rows3" = Table.SelectRows(#"Removed Columns1", each ([Qty] <> null)),

It's all about the Changed of type to a Date in Column1, then you filter Column1. If there is an error in that column, it will hit the first one, fill errors across the table and truncate the data set.

What you need to do is deal with your errors immediately after the Changed Type step, before you do any filtering. I'd select that step, scroll up and down your data set to see why there are errors. If the rows are truly garbage, then select Column1 and remove errors there before the filter. At that point I'll guess that you'll probably resolve the issue.

HTH,

 

[Martinl]

Your issue is mostly likely with the order you are doing things here:

It's all about the Changed of type to a Date in Column1, then you filter Column1. If there is an error in that column, it will hit the first one, fill errors across the table and truncate the data set.

What you need to do is deal with your errors immediately after the Changed Type step, before you do any filtering. I'd select that step, scroll up and down your data set to see why there are errors. If the rows are truly garbage, then select Column1 and remove errors there before the filter. At that point I'll guess that you'll probably resolve the issue.

HTH,

Hi Ken

I wish it were this.
It looks like it is something else though.
Looking at what you said, about "deal with your errors immediately after the Changed Type step, I notice that the errors are there at the Source step and removing errors doesn't they just remain stubbornly there.
This would suggest to me that there is an error when reading the filenames from SharePoint, before we have even opened them which seems strange to me.

Martin

 

[Ken Puls]

Hi Martin,

That is odd. So one of the things that does bother me is that you've also got multiple hard coded paths in there. I try to avoid this for updates, and I wonder if there is a potential there for errors too. Here's how I would set up my queries:

• Connect to the SharePoint URL
• Go to the Folder Path column and replace "http://sp13.emea.xxxxx.net/emea/EMDBS/Sales/" with nothing (this will let you see the folder names)
• Filter the Folder Path column down to just the folder(s) you want
• I usually convert file extensions to just Lower Case, then filter to the file type I need
• Name the query FilesList
• Go to Home --> Close & Load To... and choose to load to Connection only

This query should give you just the files that you need to consolidate. Ideally here we'd like to see no rows with errors at all.

Next you'd right click the FilesList query in the Query Pane and choose Reference.

• Name the query right away (I"ll pretend you called it Transactions)
• Click the combine files button and choose the sample file you want to use
• Delete the Changed Type step in this query.

This will create four new queries for you. The good news here is that the only file path hard coded in your solution is the initial one inside the FilePath query. In addition, there is no need to remove the columns in the FilesList query, as this will do it automatically. (If you want to change which columns get removed, edit the Removed Other Columns 1 step.)

You should then go to the Sample Transform from Transactions (or whatever you called it) to do the transformations. This will make modifications to the files BEFORE you append them.

After you're done, go back to Transactions (or whatever you called it) and re-set the Data types.

This is the best practice way to approach the task and my hope is that this will allow us to really break down exactly where the issue is.

 

[Martinl]

Hi Ken

I'm not sure I follow...

1. Connect to the SharePoint URL
2. Go to the Folder Path column and replace "http://sp13.emea.xxxxx.net/emea/EMDBS/Sales/" with nothing (this will let you see the folder names)
3. Filter the Folder Path column down to just the folder(s) you want
4. I usually convert file extensions to just Lower Case, then filter to the file type I need
5. Name the query FilesList
6. Go to Home --> Close & Load To... and choose to load to Connection only

I get stuck at point 2



Right Click on Path name
Edit

Change Tables to Files and ApiVersion to 14 (ApiVersion 15 will not list the binaries)
Immediately the last row (231) shows Error in all columns

I’m not sure where you want me to change the Folder Name to nothing (Blanks) as it is at this point that Folder names is first seen.
Until I get past this I cannot continue with your suggested troubleshoot.

Kind Regards

Martin

 

[Martinl]

Hi Ken

Same as previous post, better images....unable to edit the previous version maybe too much time had passed!

I'm not sure I follow...

1. Connect to the SharePoint URL
2. Go to the Folder Path column and replace "http://sp13.emea.xxxxx.net/emea/EMDBS/Sales/" with nothing (this will let you see the folder names)
3. Filter the Folder Path column down to just the folder(s) you want
4. I usually convert file extensions to just Lower Case, then filter to the file type I need
5. Name the query FilesList
6. Go to Home --> Close & Load To... and choose to load to Connection only

I get stuck at point 2



Right Click on Path name
Edit

Change SharePoint.Tables to SharePoint.Files and ApiVersion to 14 (ApiVersion 15 will not list the binaries)
Immediately the last row (231) shows Error in all columns

I’m not sure where you want me to change the Folder Name to nothing (Blanks) as it is at this point that Folder names is first seen.
Until I get past this I cannot continue with your suggested troubleshoot.

Kind Regards

Martin

 

[Ken Puls]

Ah, my bad Martin, I forgot that Excel 2016 doesn't have the "From SharePoint Folder" connector in the menus. I didn't realize you had to go through the list connector.

So here's the thing... I've tested with both Api v14 and v15, and I get the same items back. I'm not sure if that's because my SharePoint site is current (I'm using O365), but the Binaries show up in any case.

What happens if you do this:

Go to New Query --> From Other Sources --> Blank Query
Put the following in the formula bar:

=SharePoint.Files("http://sp13.emea.xxxxx.net/emea/EMDBS/Sales/")

Does it still give you an error on the last line? If it does, then the issue is most likely something with the site files, and I'm not sure I can help you. It will need to get involved to figure out why the folder is not returning all the items...

Please let me know what you see here...

The other thing you might want to look at... Go to you Windows Update settings --> Advanced, and make sure the box is ticked next to "Get updates for other Microsoft products". Then run a windows update. It should update your Excel 2016 to get some new Power Query features like Column from Examples. (You can find that at the beginning of Power Query's Add Column tab if the software has updated.)