Promoting First Row to Headers - inconsistent with Web Connection

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