0

I am attempting to use Excel PowerQuery to pull data from a webpage using a cell in the workbook to generate the URL. The URL is concatenated as the ID's come from another source and is currently 1043 characters long.

I am using the answer from this previous question to perform the task of getting the cell value into the query, but I receive an error as it is above 260 characters.

Is there any way around this error?

let
    Source = Excel.Workbook(File.Contents(GetValue("SourceFile"))),
    #"Removed Columns" = Table.RemoveColumns(Source,{"Name", "Item", "Kind", "Hidden"}),
    #"Expanded Data" = Table.ExpandTableColumn(#"Removed Columns", "Data", {"Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8", "Column9", "Column10", "Column11", "Column12", "Column13", "Column14", "Column15", "Column16", "Column17", "Column18", "Column19", "Column20", "Column21", "Column22", "Column23", "Column24"}, {"Data.Column1", "Data.Column2", "Data.Column3", "Data.Column4", "Data.Column5", "Data.Column6", "Data.Column7", "Data.Column8", "Data.Column9", "Data.Column10", "Data.Column11", "Data.Column12", "Data.Column13", "Data.Column14", "Data.Column15", "Data.Column16", "Data.Column17", "Data.Column18", "Data.Column19", "Data.Column20", "Data.Column21", "Data.Column22", "Data.Column23", "Data.Column24"}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Expanded Data", [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Project ID", Int64.Type}, {"Project Name", type text}, {"Project Action Status", type text}, {"ID", Int64.Type}, {"Title", type text}, {"Owner", type text}, {"Start date", Int64.Type}, {"Due date", Int64.Type}, {"Actual start date", Int64.Type}, {"Actual end date", Int64.Type}, {"Planned working time", type number}, {"Tracked working time", type number}, {"State", type text}, {"Is overdue?", type logical}, {"Milestone", type text}, {"Project group", type text}, {"Files", Int64.Type}, {"Tags", type any}, {"Predecessor tasks", type any}, {"Successor tasks", type any}, {"Last updated at", type number}, {"Last updated by", type text}, {"Description", type text}, {"Last correspondence", type text}})
in
    #"Changed Type"
Clannagh
  • 133
  • 7

2 Answers2

0

File.Contents probably has a character limit related to the filepath limit in Windows. Since you are getting data from a website though, try using Web.Contents instead of File.Contents.

Wedge
  • 1,766
  • 1
  • 8
  • 14
  • The reason for the File.Contents is the URL is dynamic but the table always has the same structure. I need to be able to pull from the dynamic URL and unsure of how to achieve that using Web.Contents – Clannagh Dec 11 '18 at 09:26
  • File.Contents has no bearing on the URL being dynamic. If you just changed File.Contents to Web.Contents it would work. The "solution" you found is functionally exactly the same as what you were doing before, just using a more direct method to get the URL from your workbook. – Wedge Dec 11 '18 at 18:08
0

I found an answer to my question here, using the following solution:

let
    MyURL = Excel.CurrentWorkbook(){[Name="PQLink"]}[Content][Column1]{0},
    Source = Excel.Workbook(Web.Contents(MyURL)),
    #"Removed Columns" = Table.RemoveColumns(Source,{"Name", "Item", "Kind", "Hidden"}),
    #"Expanded Data" = Table.ExpandTableColumn(#"Removed Columns", "Data", {"Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8", "Column9", "Column10", "Column11", "Column12", "Column13", "Column14", "Column15", "Column16", "Column17", "Column18", "Column19", "Column20", "Column21", "Column22", "Column23", "Column24"}, {"Data.Column1", "Data.Column2", "Data.Column3", "Data.Column4", "Data.Column5", "Data.Column6", "Data.Column7", "Data.Column8", "Data.Column9", "Data.Column10", "Data.Column11", "Data.Column12", "Data.Column13", "Data.Column14", "Data.Column15", "Data.Column16", "Data.Column17", "Data.Column18", "Data.Column19", "Data.Column20", "Data.Column21", "Data.Column22", "Data.Column23", "Data.Column24"}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Expanded Data", [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Project ID", Int64.Type}, {"Project Name", type text}, {"Project Action Status", type text}, {"ID", Int64.Type}, {"Title", type text}, {"Owner", type text}, {"Start date", Int64.Type}, {"Due date", Int64.Type}, {"Actual start date", Int64.Type}, {"Actual end date", Int64.Type}, {"Planned working time", type number}, {"Tracked working time", type number}, {"State", type text}, {"Is overdue?", type logical}, {"Milestone", type text}, {"Project group", type text}, {"Files", Int64.Type}, {"Tags", type any}, {"Predecessor tasks", type any}, {"Successor tasks", type any}, {"Last updated at", type number}, {"Last updated by", type text}, {"Description", type text}, {"Last correspondence", type text}})
in
    #"Changed Type"
Clannagh
  • 133
  • 7