Adding step results in empty table

[dorunrun]

We have a very simple query retrieving SharePoint list items. Whenever a "Changed Type" step or an index column is added it results in an empty table. Location of the steps does not seem to matter. When the steps are added the table is empty. Any ideas?

 

[Ken Puls]

I don't have any Sharepoint lists to test against, but that seems weird, for sure.

You might be able to get a behaviour change if you buffer the table before doing the operation, although this should not be necessary. If it's reproducible, then it should definitely get reported to the Excel team.

To buffer your table, click the fx icon in the formula bar inside Power Query. Then type =Table.Buffer(<previous step name>)

Be aware that if the previous step in the Applied Steps window has a space in it, you'll need to wrap it in #" ". I.e. a step called "Removed Duplicates" would need to be = Table.Buffer(#"Removed Duplicates")

 

[miguel]

Hey!

Would you mind posting your M code here? To do that, go to the view Tab--> Advanced Editor --> copy everything inside that window and paste it in your response to this thread.

Best

 

[dorunrun]

Just a little more background. I am in a corporate enviorment so there could be some modifications to the office install. This was a project for a summer intern that originally had 32 bit office installed. Currently, it is this single user that is having the problem so we uninstalled 32 bit and re installed 64 bit (32 bit is the companies default but 64 bit can be requested). At first it was just the added index step that caused the empty table now it is also happening at the changed type step. We tried to buffer the table and still had the same problem.

We've tested on several other systems both 32 and 64 bit and the query seems to run fine. Based on experience with our laptop images it could definitely be limited to this single user because I've been using PQ for several years and we've never encountered another issue like this. However, as we implement tools to a larger user group we're worried that this could be a larger problem.

let
    Source = SharePoint.Tables([URL="http://collabsite/"]http://CollabSite[/URL], [ApiVersion = 14]),
    ProjectKeyEvents1 = Source{[Name="ProjectKeyEvents"]}[Content],
        //Remove Unused Columns
    #"Removed Unused Columns" = Table.RemoveColumns(ProjectKeyEvents1,{"ContentTypeID", "ContentType", "Modified", "Created", "CreatedById", "ModifiedById", "Owshiddenversion", "Version", "Path", "BusinessUnitIntendedBusinessUnit", "CreatedBy", "ModifiedBy", "Attachments", "Id"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Removed Unused Columns",{"Date", "DescriptionOfEvent", "AssetProject", "BusinessUnitIntendedBusinessUnitValue", "DisplayInVisual", "EventOutcome", "EventResolved"}),
        //Sort rows by Event Date        
    #"Sorted Rows" = Table.Sort(#"Reordered Columns",{{"BusinessUnitIntendedBusinessUnitValue", Order.Ascending}, {"Date", Order.Ascending}}),
        //Calculate Quarter of Year
    #"Calc Qtr Num" = Table.AddColumn(#"Sorted Rows", "Quarter", each Date.QuarterOfYear([Date])),
        //Change Quarter Number to Text for concatenation
    #"Changed Type" = Table.TransformColumnTypes(#"Calc Qtr Num",{{"Quarter", type text}, {"Date", type date}}),
        //Concatenate Quarter Name
    #"Concat Q and Num" = Table.AddColumn(#"Changed Type", "QuarterOfYear", each "Q"&[Quarter]),
        //Remove Numeric Quarter Column
    #"Removed Columns1" = Table.RemoveColumns(#"Concat Q and Num",{"Quarter"}),
        //Calculate Month Name
    #"Calc Month Name" = Table.AddColumn(#"Removed Columns1", "MonthName", each Date.MonthName([Date])),
        //Calculate Day of Month
    #"Calc Day of Month" = Table.AddColumn(#"Calc Month Name", "Day", each Date.Day([Date])),
        //Add Index
    #"Added Index" = Table.AddIndexColumn(#"Calc Day of Month", "Index", 1, 1),
    #"Reordered Columns1" = Table.ReorderColumns(#"Added Index",{"Date", "AssetProject", "BusinessUnitIntendedBusinessUnitValue", "DescriptionOfEvent", "Index", "DisplayInVisual", "EventOutcome", "EventResolved"}),
    #"Sorted Rows1" = Table.Sort(#"Reordered Columns1", {"Date", Order.Ascending}),
    #"Grouped Rows" = Table.Group(#"Sorted Rows1", {"Date"}, {{"DateTable", each Table.AddIndexColumn(_, "DateIndex",1,1), type table}}),
        // After interactively Grouping by Rows (for each Date), the M code was manually modified as described below.
        // The following was changed:
        //   each _,
        // to:
        //   each Table.AddIndexColumn(_, "DateIndex",1,1), 
    #"Expanded DateTable" = Table.ExpandTableColumn(#"Grouped Rows", "DateTable", {"AssetProject", "BusinessUnitIntendedBusinessUnitValue", "DescriptionOfEvent", "Index", "DisplayInVisual", "EventOutcome", "QuarterOfYear", "MonthName", "Day", "EventResolved", "DateIndex"}, {"AssetProject", "BusinessUnitIntendedBusinessUnitValue", "DescriptionOfEvent", "Index", "DisplayInVisual", "EventOutcome", "QuarterOfYear", "MonthName", "Day", "EventResolved", "DateIndex"}),
    #"Reordered Columns2" = Table.ReorderColumns(#"Expanded DateTable",{"Date", "DateIndex", "AssetProject", "BusinessUnitIntendedBusinessUnitValue", "DescriptionOfEvent", "Index", "DisplayInVisual", "EventOutcome", "QuarterOfYear", "MonthName", "Day", "EventResolved"})
in
    #"Reordered Columns2"

 

[miguel]

Hey!
Sorry for the long wait. I never received a notification that this thread was updated and I totally forgot about it.


I'd recommend that you try loading that SharePoint List as a completely new query and see the steps that PQ automatically adds. That way you can tell that you can definitely add data from sharepoint without any issues.


Before the Removed Unused columns there should be a step to rename columns. What happens with SharePoint is that it stores 2 columns by the same name: "Id" - they are in different contexts, but when Power Query loads them they are in the same table and that creates issues.

This causes conflict in Power Query as you can't have 2 columns with the same name in it, so PQ automatically adds a step to rename one of those columns when you load the list. I'm guessing that someone deleted that "Rename Columns" step after the initial load and that's what's causing the issues.

tl;dr - before the remove unusued columns, rename one of the ID columns and that should fix the issue. The formula of that step should be something like: = Table.RenameColumns(#"66918e87-8c1b-49c9-a4f1-3e93196eabdf",{{"ID", "ID.1"}})

Alternatively, If that doesn't fix it, I'd recommend that you try checking step by step to see where the error occurs. Power Query will throw an error on the step that has the error and any subsequent step, so you need to find the first step with the error and that can help you pinpoint what step is causing the issue - then you can get inside the code and see what is doing and why is causing the issue.

 

[dorunrun]

Thanks Miguel. I'll give those suggestions a try. Just to clarify, there is no error. When the index step is added the table is empty almost like all the rows were filtered
Kevin

 

[miguel]

that sounds really strange. So you mean that when you add the index on the "Added Index" step you just get a completely blank table?