我已将 Power Query 设置为引用单元格值作为 URL，有没有办法也可以引用自定义列中的单元格值？
let
URLPart = Excel.CurrentWorkbook(){[Name="URLParameter"]}[Content]{0}[Column1],
Source = Web.BrowserContents(URLPart),
#"Extracted Table From Html" = Html.Table(Source, {{"Column1", "DIV.fixtures-table.table-scroll > TABLE > * > TR > TH:not([colspan]):not([rowspan]):nth-child(1):nth-last-child(6), DIV.fixtures-table.table-scroll > TABLE > * > TR > TD:not([colspan]):not([rowspan]):nth-child(1):nth-last-child(8)"}, {"Column2", "DIV.fixtures-table.table-scroll > TABLE > * > TR > TH:not([colspan]):not([rowspan]):nth-child(1):nth-last-child(6) + TH:not([colspan]):not([rowspan]):nth-child(2):nth-last-child(5), DIV.fixtures-table.table-scroll > TABLE > * > TR > TD:not([colspan]):not([rowspan]):nth-child(1):nth-last-child(8) + TD:not([colspan]):not([rowspan]):nth-child(2):nth-last-child(7)"}, {"Column3", "DIV.fixtures-table.table-scroll > TABLE > * > TR > TH:not([colspan]):not([rowspan]):nth-child(1):nth-last-child(6) + TH:not([colspan]):not([rowspan]):nth-child(2):nth-last-child(5) + TH[colspan=""2""]:not([rowspan]):nth-child(3):nth-last-child(4), DIV.fixtures-table.table-scroll > TABLE > * > TR > TD:not([colspan]):not([rowspan]):nth-child(1):nth-last-child(8) + TD:not([colspan]):not([rowspan]):nth-child(2):nth-last-child(7) + TD:not([colspan]):not([rowspan]):nth-child(3):nth-last-child(6)"}, {"Column4", "DIV.fixtures-table.table-scroll > TABLE > * > TR > TH:not([colspan]):not([rowspan]):nth-child(1):nth-last-child(6) + TH:not([colspan]):not([rowspan]):nth-child(2):nth-last-child(5) + TH[colspan=""2""]:not([rowspan]):nth-child(3):nth-last-child(4), DIV.fixtures-table.table-scroll > TABLE > * > TR > TD:not([colspan]):not([rowspan]):nth-child(1):nth-last-child(8) + TD:not([colspan]):not([rowspan]):nth-child(2):nth-last-child(7) + TD:not([colspan]):not([rowspan]):nth-child(3):nth-last-child(6) + TD:not([colspan]):not([rowspan]):nth-child(4):nth-last-child(5)"}, {"Column5", "DIV.fixtures-table.table-scroll > TABLE > * > TR > TH:not([colspan]):not([rowspan]):nth-child(1):nth-last-child(6) + TH:not([colspan]):not([rowspan]):nth-child(2):nth-last-child(5) + TH[colspan=""2""]:not([rowspan]):nth-child(3):nth-last-child(4) + TH:not([colspan]):not([rowspan]):nth-child(4):nth-last-child(3), DIV.fixtures-table.table-scroll > TABLE > * > TR > TD:not([colspan]):not([rowspan]):nth-child(1):nth-last-child(8) + TD:not([colspan]):not([rowspan]):nth-child(2):nth-last-child(7) + TD:not([colspan]):not([rowspan]):nth-child(3):nth-last-child(6) + TD:not([colspan]):not([rowspan]):nth-child(4):nth-last-child(5) + TD:not([colspan]):not([rowspan]):nth-child(5):nth-last-child(4)"}, {"Column6", "DIV.fixtures-table.table-scroll > TABLE > * > TR > TH:not([colspan]):not([rowspan]):nth-child(1):nth-last-child(6) + TH:not([colspan]):not([rowspan]):nth-child(2):nth-last-child(5) + TH[colspan=""2""]:not([rowspan]):nth-child(3):nth-last-child(4) + TH:not([colspan]):not([rowspan]):nth-child(4):nth-last-child(3) + TH[colspan=""2""]:not([rowspan]):nth-child(5):nth-last-child(2), DIV.fixtures-table.table-scroll > TABLE > * > TR > TD:not([colspan]):not([rowspan]):nth-child(1):nth-last-child(8) + TD:not([colspan]):not([rowspan]):nth-child(2):nth-last-child(7) + TD:not([colspan]):not([rowspan]):nth-child(3):nth-last-child(6) + TD:not([colspan]):not([rowspan]):nth-child(4):nth-last-child(5) + TD:not([colspan]):not([rowspan]):nth-child(5):nth-last-child(4) + TD:not([colspan]):not([rowspan]):nth-child(6):nth-last-child(3)"}, {"Column7", "DIV.fixtures-table.table-scroll > TABLE > * > TR > TH:not([colspan]):not([rowspan]):nth-child(1):nth-last-child(6) + TH:not([colspan]):not([rowspan]):nth-child(2):nth-last-child(5) + TH[colspan=""2""]:not([rowspan]):nth-child(3):nth-last-child(4) + TH:not([colspan]):not([rowspan]):nth-child(4):nth-last-child(3) + TH[colspan=""2""]:not([rowspan]):nth-child(5):nth-last-child(2), DIV.fixtures-table.table-scroll > TABLE > * > TR > TD:not([colspan]):not([rowspan]):nth-child(1):nth-last-child(8) + TD:not([colspan]):not([rowspan]):nth-child(2):nth-last-child(7) + TD:not([colspan]):not([rowspan]):nth-child(3):nth-last-child(6) + TD:not([colspan]):not([rowspan]):nth-child(4):nth-last-child(5) + TD:not([colspan]):not([rowspan]):nth-child(5):nth-last-child(4) + TD:not([colspan]):not([rowspan]):nth-child(6):nth-last-child(3) + TD:not([colspan]):not([rowspan]):nth-child(7):nth-last-child(2)"}, {"Column8", "DIV.fixtures-table.table-scroll > TABLE > * > TR > TH:not([colspan]):not([rowspan]):nth-child(1):nth-last-child(6) + TH:not([colspan]):not([rowspan]):nth-child(2):nth-last-child(5) + TH[colspan=""2""]:not([rowspan]):nth-child(3):nth-last-child(4) + TH:not([colspan]):not([rowspan]):nth-child(4):nth-last-child(3) + TH[colspan=""2""]:not([rowspan]):nth-child(5):nth-last-child(2) + TH:not([colspan]):not([rowspan]):nth-child(6):nth-last-child(1), DIV.fixtures-table.table-scroll > TABLE > * > TR > TD:not([colspan]):not([rowspan]):nth-child(1):nth-last-child(8) + TD:not([colspan]):not([rowspan]):nth-child(2):nth-last-child(7) + TD:not([colspan]):not([rowspan]):nth-child(3):nth-last-child(6) + TD:not([colspan]):not([rowspan]):nth-child(4):nth-last-child(5) + TD:not([colspan]):not([rowspan]):nth-child(5):nth-last-child(4) + TD:not([colspan]):not([rowspan]):nth-child(6):nth-last-child(3) + TD:not([colspan]):not([rowspan]):nth-child(7):nth-last-child(2) + TD:not([colspan]):not([rowspan]):nth-child(8):nth-last-child(1)"}}, [RowSelector="DIV.fixtures-table.table-scroll > TABLE > * > TR"]),
#"Promoted Headers" = Table.PromoteHeaders(#"Extracted Table From Html", [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Type", type text}, {"Date / Time", type datetime}, {"Home Team", type text}, {"Home Team_1", type text}, {"", type text}, {"Away Team", type text}, {"Away Team_2", type text}, {"Venue", type text}}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Date / Time", "DATE"}}),
#"Split Column by Delimiter" = Table.SplitColumn(Table.TransformColumnTypes(#"Renamed Columns", {{"DATE", type text}}, "en-GB"), "DATE", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"DATE.1", "DATE.2"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"DATE.1", type date}, {"DATE.2", type time}}),
#"Renamed Columns1" = Table.RenameColumns(#"Changed Type1",{{"DATE.2", "KICK OFF"}, {"DATE.1", "DATE"}, {"Type", "COMPETITION"}}),
#"Removed Columns" = Table.RemoveColumns(#"Renamed Columns1",{"", "Home Team_1", "Away Team"}),
#"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"DATE", "KICK OFF", "Home Team", "Away Team_2", "COMPETITION", "Venue"}),
#"Added Custom" = Table.AddColumn(#"Reordered Columns", "Custom", each if[Home Team]="Abbey Rangers" then "H" else "A"),
#"Renamed Columns2" = Table.RenameColumns(#"Added Custom",{{"Custom", "HOME/AWAY"}}),
#"Reordered Columns1" = Table.ReorderColumns(#"Renamed Columns2",{"DATE", "KICK OFF", "Home Team", "Away Team_2", "COMPETITION", "HOME/AWAY", "Venue"}),
#"Added Custom1" = Table.AddColumn(#"Reordered Columns1", "OPPOSITION", each if [Home Team]="Abbey Rangers" then [Away Team_2] else [Home Team]),
#"Reordered Columns2" = Table.ReorderColumns(#"Added Custom1",{"DATE", "KICK OFF", "OPPOSITION", "Home Team", "Away Team_2", "COMPETITION", "HOME/AWAY", "Venue"}),
#"Removed Columns1" = Table.RemoveColumns(#"Reordered Columns2",{"Home Team", "Away Team_2", "Venue"}),
#"Reordered Columns3" = Table.ReorderColumns(#"Removed Columns1",{"DATE", "KICK OFF", "OPPOSITION", "HOME/AWAY", "COMPETITION"}),
#"Added Custom2" = Table.AddColumn(#"Reordered Columns3", "Custom", each if[COMPETITION]="L" then "League" else if[COMPETITION]="SCOM" then "Southern Combination Challenge Cup" else [COMPETITION]),
#"Removed Columns2" = Table.RemoveColumns(#"Added Custom2",{"COMPETITION"}),
#"Renamed Columns3" = Table.RenameColumns(#"Removed Columns2",{{"Custom", "COMPETITION"}})
in
#"Renamed Columns3"
专门寻找
#"Added Custom" = Table.AddColumn(#"Reordered Columns", "Custom", each if[Home Team]="Abbey Rangers" then "H" else "A"),
我希望它引用单元格 F4 中的值，而不是“Abbey Rangers”
鉴于我已经引用了一个 URL，我不确定该怎么做。我尝试了下面的方法，使用了一个名为 FullTimeName 的单元格，但不太正确
= Table.AddColumn(#"Reordered Columns", "Custom", each if[Home Team]="FullTimeName" then "H" else "A")
您可以创建一个命名范围并使用以下代码来提取其内容：
在你的特定情况下会是这样的：