#web-scraping #powerquery #powerbi-desktop #m
Вопрос:
Вот мой код функции. У меня есть еще одна таблица со всеми моими URL-адресами. Но когда я пытаюсь подключить свои URL CarData -адреса, он возвращается Please specify how to connect и показывает мне это окно. Что — то, что я хотел бы не делать вручную для тысяч URL-адресов. Почему он просит об этом и как мне автоматизировать его для тысяч моих URL-адресов?
(CarData as text)=>
let
Source = Web.BrowserContents(CarData),
#"Extracted Table From Html" = Html.Table(Source, {{"Column1", ".key"}, {"Column2", ".value"}}, [RowSelector=".key"]),
#"Changed Type" = Table.TransformColumnTypes(#"Extracted Table From Html",{{"Column1", type text}, {"Column2", type text}}),
#"Transposed Table" = Table.Transpose(#"Changed Type"),
#"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table", [PromoteAllScalars=true]),
#"Split Column by Delimiter" = Table.SplitColumn(#"Promoted Headers", "Gas Mileage", Splitter.SplitTextByDelimiter("/", QuoteStyle.Csv), {"Gas Mileage.1", "Gas Mileage.2"}),
#"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Gas Mileage.1", type text}, {"Gas Mileage.2", type text}}),
#"Replaced Value" = Table.ReplaceValue(#"Changed Type2","Transmission:","",Replacer.ReplaceText,{"Transmission"}),
#"Split Column by Delimiter1" = Table.SplitColumn(#"Replaced Value", "SAE Net Torque @ RPM", Splitter.SplitTextByDelimiter("@", QuoteStyle.Csv), {"SAE Net Torque @ RPM.1", "SAE Net Torque @ RPM.2"}),
#"Changed Type3" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"SAE Net Torque @ RPM.1", Int64.Type}, {"SAE Net Torque @ RPM.2", Int64.Type}}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type3",{{"SAE Net Torque @ RPM.1", "SAE Net Torque"}, {"SAE Net Torque @ RPM.2", "SAE Torque RPM"}}),
#"Split Column by Delimiter2" = Table.SplitColumn(#"Renamed Columns", "SAE Net Horsepower @ RPM", Splitter.SplitTextByDelimiter("@", QuoteStyle.Csv), {"SAE Net Horsepower @ RPM.1", "SAE Net Horsepower @ RPM.2"}),
#"Changed Type4" = Table.TransformColumnTypes(#"Split Column by Delimiter2",{{"SAE Net Horsepower @ RPM.1", Int64.Type}, {"SAE Net Horsepower @ RPM.2", Int64.Type}}),
#"Renamed Columns1" = Table.RenameColumns(#"Changed Type4",{{"SAE Net Horsepower @ RPM.1", "SAE Net Horsepower"}, {"SAE Net Horsepower @ RPM.2", "SAE Net Horsepower RPM"}, {"Displacement", "Displacement (L)"}}),
#"Split Column by Delimiter3" = Table.SplitColumn(#"Renamed Columns1", "Displacement (L)", Splitter.SplitTextByDelimiter("/", QuoteStyle.Csv), {"Displacement (L).1", "Displacement (L).2"}),
#"Changed Type5" = Table.TransformColumnTypes(#"Split Column by Delimiter3",{{"Displacement (L).1", type text}, {"Displacement (L).2", Int64.Type}}),
#"Renamed Columns2" = Table.RenameColumns(#"Changed Type5",{{"Displacement (L).2", "Displacement"}}),
#"Changed Type6" = Table.TransformColumnTypes(#"Renamed Columns2",{{"Displacement", Int64.Type}}),
#"Replaced Value1" = Table.ReplaceValue(#"Changed Type6","L","",Replacer.ReplaceText,{"Displacement (L).1"}),
#"Changed Type7" = Table.TransformColumnTypes(#"Replaced Value1",{{"Displacement (L).1", type number}}),
#"Renamed Columns3" = Table.RenameColumns(#"Changed Type7",{{"Gas Mileage.1", "mpg city"}, {"Gas Mileage.2", "mpg Hwy"}}),
#"Replaced Value2" = Table.ReplaceValue(#"Renamed Columns3","mpg Hwy","",Replacer.ReplaceText,{"mpg Hwy"}),
#"Replaced Value3" = Table.ReplaceValue(#"Replaced Value2","mpg City","",Replacer.ReplaceText,{"mpg city"}),
#"Changed Type8" = Table.TransformColumnTypes(#"Replaced Value3",{{"mpg city", Int64.Type}, {"mpg Hwy", Int64.Type}})
in
#"Changed Type8"
Вот код для моих URL-адресов, если он вам нужен;
let
Source = Binary.Decompress(File.Contents("pathtrims.xml.gz"), Compression.GZip),
#"Imported CSV" = Xml.Document(Source),
Value = #"Imported CSV"{0}[Value],
#"Removed Columns" = Table.RemoveColumns(Value,{"Attributes"}),
#"Removed Other Columns" = Table.SelectColumns(#"Removed Columns",{"Value"}),
#"Expanded Value" = Table.ExpandTableColumn(#"Removed Other Columns", "Value", {"Name", "Namespace", "Value", "Attributes"}, {"Name", "Namespace", "Value.1", "Attributes"}),
#"Removed Other Columns1" = Table.SelectColumns(#"Expanded Value",{"Value.1"}),
#"Renamed Columns" = Table.RenameColumns(#"Removed Other Columns1",{{"Value.1", "URLs"}}),
#"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"URLs", type text}}),
#"Invoked Custom Function" = Table.AddColumn(#"Changed Type", "Data", each #"Table 3"([URLs]))
in
#"Invoked Custom Function"
Ответ №1:
Попробуйте это решение, оно не точное, но это аналогичная ошибка;