Tenho um Power Query configurado para referenciar um valor de célula como URL. Existe uma maneira de também referenciar um valor de célula em uma coluna personalizada?
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"
Procurando especificamente pela linha
#"Added Custom" = Table.AddColumn(#"Reordered Columns", "Custom", each if[Home Team]="Abbey Rangers" then "H" else "A"),
Em vez de "Abbey Rangers", gostaria que ele fizesse referência ao valor na célula F4
Só não tenho certeza de como fazer isso, já que já referenciei uma URL. Tentei o abaixo com uma célula chamada FullTimeName, mas não está muito certo
= Table.AddColumn(#"Reordered Columns", "Custom", each if[Home Team]="FullTimeName" then "H" else "A")