Promoting First Row to Headers - inconsistent with Web Connection

[Merchy2401]

Hi All,

I am connecting to a number (100+) sharepoint 2007 lists using the Web Connection method and in some cases it connects to the table I need and identifies the headers in the table all good!

Other times with a different Sharepint 2007 site it will not recognise the headers & I have to manually (part of the query steps) promote the first row to headers and then can apply the rest of my usual query steps

Then (weirdly) at some undefined point in the future, the table where I had to promote the first line to headers will fail on a refresh because it cant find a column name I have defined in a query step.... I go into to query editor and the table has now recognised the headers automatically & the promote first row to headers is overwriting the headers with the first row of data!! so I now have to amend the query to remove the "promote" query step.

It seems really inconsistent and is preventing me from using a Custom Function to automate the many table loads Im doing as I cant seem to get consistency in having to promote headers or not..

All Sharepoint sites Im connecting to are in the same sharepoint collection/site in the same server farm.

Any idea's from you clever guys & girls out there as to what might be happening & how I can stop it?

Thanks in advance,

Andi 

[Merchy2401]

Hi All,

I am connecting to a number (100+) sharepoint 2007 lists using the Web Connection method and in some cases it connects to the table I need and identifies the headers in the table all good!

Other times with a different Sharepint 2007 site it will not recognise the headers & I have to manually (part of the query steps) promote the first row to headers and then can apply the rest of my usual query steps

Then (weirdly) at some undefined point in the future, the table where I had to promote the first line to headers will fail on a refresh because it cant find a column name I have defined in a query step.... I go into to query editor and the table has now recognised the headers automatically & the promote first row to headers is overwriting the headers with the first row of data!! so I now have to amend the query to remove the "promote" query step.

It seems really inconsistent and is preventing me from using a Custom Function to automate the many table loads Im doing as I cant seem to get consistency in having to promote headers or not..

All Sharepoint sites Im connecting to are in the same sharepoint collection/site in the same server farm.

Any idea's from you clever guys & girls out there as to what might be happening & how I can stop it?

Thanks in advance,

Andi 

[v-jiascu-msft]

Hi Andi, 

It's weird as we can see from your description. I'm afraid a sample doesn't help much. 

Did you create a custom connector? Can you share the code? Please mask the sensitive parts.

The Sharepoint 2007 should be an on-premises source, isn't it?

How did you connect using a Web connection? There could be many details.

Best Regards,

[Merchy2401]

HI @v-jiascu-msft

thanks for the response & apologies for my slow reply (busy busy busy!!)

Its happened again today, hence my prompt to reply back to this thread.... this time to a connection that I have had in place for over 6months where I had to promote first lines to headers now recognises the headers automatically & the promote headers now removes my headers & replaces withthe first data record.

Im checking with my sharepoint team if anything at all has changed in the last 24hours on that site...

So, I am using the following..

• Power BI Desktop November 2018 edition (but it been happening for months & months)
• Out of the box Web Connection in the desktop client
• Connecting to a Sharepoint 2007 list with a public view that removes any limits on records returned etc
• Sharepoint 2007 is on-premise
• I refresh my dataset in the power BI service using Desktop Gateway; but the same issue happens if I refresh my dataset directly in the desktop client (Currently using November 2018 version of Gateway)

Code for the connextion/data query below... (it is very basic!)

let
Source = Web.Page(Web.Contents("http://x.y.z/sites/Engagements/abc/Lists/Portfolio%20Tracking%20Tool/PowerBI.aspx")),
Data1 = Source{1}[Data],
#"Promoted Headers" = Table.PromoteHeaders(Data1, [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Pulse ID", type text}, {"Project Reference No.", type text}, {"Company", type text}, {"Programme", type text}, {"Business Unit", type text}, {"Portfolio", type text}, {"Project Name", type text}, {"Project Description", type text}, {"Sensitive Project", type text}, {"Project Classification", type text}, {"Customer Priority", type text}, {"Project Position", type text}, {"Project Status", type text}, {"Current Stage", type text}, {"Pipeline Conversion Probability", type text}, {"Governance Level", type text}, {"Project Engagement", type text}, {"Project Complexity", type text}, {"CC LOB", type text}, {"Customer Required Start Date", type date}, {"Modified", type datetime}, {"Next Review Date", type date}, {"SIA Required", type text}, {"SIA Meeting Date", type text}, {"GIO Supported Product", type text}, {"Level of Impact", type text}, {"Service Acceptance Required", type text}, {"Customer Service Level Manager", type text}, {"Change to GSD Support", type text}, {"Change to Contracted Service", type text}, {"CTS Review", type text}, {"Requested Live Service Date", type date}, {"Forecast/Actual Live Service Date", type date}, {"Change to Service/ST Requirements", type text}, {"Change to Service/ST Status", type text}, {"Date and Time Request Received", type datetime}, {"Customer Reference Number", type text}, {"Campaign Initiation Review Date", type datetime}, {"Triage Review Date", type date}, {"TDW Date", type text}, {"SLA Start Date", type date}, {"Baseline SLA Date", type date}, {"Revised SLA Date", type date}, {"Issue Date 1", type date}, {"Requestor Name", type text}, {"Requestor Job Title", type text}, {"Requestor Tel", type text}, {"Requestor Mob", type text}, {"Requestor Email", type text}, {"Pre Sales RAG", type text}, {"Pre Sales Narrative", type text}, {"Pre Sales Notes", type text}, {"Project Billing", type text}, {"Billing Profile", type text}, {"Submission Review Date", type datetime}, {"Pre Sales On Hold Reason", type text}, {"PS On Hold Date 1", type date}, {"PS On Hold 1 Chase 1", type date}, {"PS On Hold 1 Chase 2", type text}, {"PS On Hold 1 Chase 3", type text}, {"PS Off Hold Date 1", type date}, {"PS Days On Hold 1", type number}, {"PS On Hold Date 2", type date}, {"PS On Hold 2 Chase 1", type text}, {"PS On Hold 2 Chase 2", type text}, {"PS On Hold 2 Chase 3", type text}, {"PS Off Hold Date 2", type text}, {"PS Days On Hold 2", type number}, {"PS On Hold Date 3", type text}, {"PS On Hold 3 Chase 1", type text}, {"PS On Hold 3 Chase 2", type text}, {"PS On Hold 3 Chase 3", type text}, {"PS Off Hold Date 3", type text}, {"PS Days On Hold 3", Int64.Type}, {"PS Total Days on Hold", type number}, {"Released to Internal QA Date", type date}, {"PMO Manager Approval Date", type date}, {"SM Approval Date", type date}, {"Technical Approval Date", type date}, {"Commercial Approval date", type date}, {"Forecast Start Date", type date}, {"Estimated Duration (Wks)", type number}, {"Expiry Date", type date}, {"Issue 1 Chase 1", type date}, {"Issue 1 Chase 2", type date}, {"Issue 1 Chase 3", type text}, {"Customer Response 1", type text}, {"Response Date 1", type date}, {"Issue Date 2", type text}, {"Issue 2 Chase 1", type text}, {"Issue 2 Chase 2", type text}, {"Issue 2 Chase 3", type text}, {"Customer Response 2", type text}, {"Response Date 2", type text}, {"Issue Date 3", type text}, {"Issue 3 Chase 1", type text}, {"Issue 3 Chase 2", type text}, {"Issue 3 Chase 3", type text}, {"Customer Response 3", type text}, {"Response Date 3", type text}, {"Pre Sales Closure Reason", type text}, {"Pre Sales Closure Narrative", type text}, {"Pre Sales Date Closed", type date}, {"AtP Date", type date}, {"Purchase Order No.", type text}, {"Delivery WBS Code", type text}, {"Delivery NAN Code", type text}, {"Service Acceptance NAN Code", type text}, {"Quotation WBS Code", type text}, {"Quotation NAN Code", type text}, {"Start Date", type date}, {"Requested Completion Date", type date}, {"Forecast Completion Date", type date}, {"Actual Delivery Date", type date}, {"Customer Project Manager", type text}, {"Customer PM Email", type text}, {"Commercial Status", type text}, {"Status Summary", type text}, {"Working at Risk Requested", type date}, {"Working at Risk Start", type date}, {"Working at Risk Expiry", type date}, {"Working at Risk Limit", Currency.Type}, {"CC Working at Risk Approver", type text}, {"Cust Working at Risk Requestor", type text}, {"Project on Hold Narrative", type text}, {"Project On Hold Date 1", type date}, {"Project Off Hold Date 1", type date}, {"Project Days On Hold 1", type number}, {"Project On Hold Date 2", type date}, {"Project Off Hold Date 2", type text}, {"Project Days On Hold 2", type number}, {"Project On Hold Date 3", type text}, {"Project Off Hold Date 3", type text}, {"Project Days On Hold 3", Int64.Type}, {"Total Project Days On Hold", type number}, {"Outlook", type text}, {"Overall RAG Trend", type text}, {"RAG Narrative", type text}, {"Return to Green Plan", type text}, {"Resourcing", type text}, {"RAG Customer Resource", type text}, {"Timeline", type text}, {"Financial", type text}, {"SoW / Scope", type text}, {"Customer View", type text}, {"Achieved This Report Period", type text}, {"Planned Next Report Period", type text}, {"Internal Notes", type text}, {"External Notes", type text}, {"Management Notes", type text}, {"Design Doc Reqd", type text}, {"Design Expected", type text}, {"Design Submitted", type text}, {"Design Response", type text}, {"Design Response Date", type text}, {"Closure Report Issued", type date}, {"Closure Report Approved", type text}, {"eCSQ Issued", type text}, {"eCSQ Date", type text}, {"eCSQ Return", type text}, {"eCSQ Score (Avg)", type text}, {"Project Closure", type date}, {"Proj Closure Reason", type text}, {"Best Practice Candidate", type text}, {"Delivery % Complete", Percentage.Type}, {"Reporting Level", type text}, {"Program Manager", type text}, {"Quote Writer", type text}, {"Project Manager", type text}, {"Lead Project Manager", type text}, {"ST-Manager", type text}, {"Pre QR", type text}, {"Project Risk Profile", type text}, {"Risk Profile Narrative", type text}, {"Service Manager", type text}, {"SMART Reference", type text}, {"CCN Reference", type text}, {"Est Quote Issue Date", type date}, {"Finance Record Created", type text}, {"Commercial Assurer", type text}, {"Implementation Designer", type text}, {"Implementation Owner", type text}, {"Opportunity Design Lead", type text}, {"Opportunity Owner", type text}, {"Opportunity Security Designer", type text}, {"Opportunity Service Designer", type text}, {"Opportunity Technical Designer", type text}, {"Opportunity Testing Specialist", type text}, {"Opportunity Tooling Designer", type text}, {"Checkpoint 1 Approach", type text}, {"Checkpoint 1 Date", type date}, {"Checkpoint 2 Approach", type text}, {"Checkpoint 2 Date", type text}, {"Checkpoint 3 Approach", type text}, {"Checkpoint 3 Date", type text}, {"Checkpoint 4 Approach", type text}, {"Checkpoint 4 Date", type text}, {"Checkpoint 5 Approach", type text}, {"Checkpoint 5 Date", type text}, {"Checkpoint 6 Approach", type text}, {"Checkpoint 6 Date", type date}, {"Cost Centre", type text}, {"Dependencies", type text}, {"Billing Plan", type text}, {"Team Morale", type text}, {"Solution", type text}, {"Final Invoice Sent", type text}, {"Billing Entity", type text}, {"Contract", type text}, {"Customer Budget Owner", type text}, {"Portfolio Lead", type text}, {"Region", type text}, {"Can this request be completed Offshore?", type text}, {"DR Required?", type text}, {"ISPs Required for Delivery", type text}, {"Licence Implications?", type text}, {"Requested Timescale Viable?", type text}, {"Triage Narrative", type text}, {"Triage Outcome", type text}, {"Triage Status", type text}, {"Change Ownership", type text}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Key", each Text.Combine({[Company],[#"Project Reference No."]}, ":")),
#"Removed Duplicates" = Table.Distinct(#"Added Custom", {"Key"})
in
#"Removed Duplicates"

again, any help/ideas/pointers happily taken on board

thanks

Andi