I have a Power Query set up to reference a cell value as the URL, is there a way to also reference a cell value in a Custom Column?
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"
Specifically looking for the line
#"Added Custom" = Table.AddColumn(#"Reordered Columns", "Custom", each if[Home Team]="Abbey Rangers" then "H" else "A"),
Instead of "Abbey Rangers" I'd like it to reference the value in cell F4
I'm just not sure how to go about it given that I've referenced a URL already. I have tried the below with a cell named FullTimeName but it's not quite right
= Table.AddColumn(#"Reordered Columns", "Custom", each if[Home Team]="FullTimeName" then "H" else "A")
I have a Power Query set up to reference a cell value as the URL, is there a way to also reference a cell value in a Custom Column?
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"
Specifically looking for the line
#"Added Custom" = Table.AddColumn(#"Reordered Columns", "Custom", each if[Home Team]="Abbey Rangers" then "H" else "A"),
Instead of "Abbey Rangers" I'd like it to reference the value in cell F4
I'm just not sure how to go about it given that I've referenced a URL already. I have tried the below with a cell named FullTimeName but it's not quite right
= Table.AddColumn(#"Reordered Columns", "Custom", each if[Home Team]="FullTimeName" then "H" else "A")
1 Answer
Reset to default 0You can create a named range and use the following code to extract its content:
let
FullNameValue = Excel.CurrentWorkbook(){[Name="FullName"]}[Content]{0}[Column1]
in
FullNameValue
In your particular case would be something like:
FullNameValue = Excel.CurrentWorkbook(){[Name="FullName"]}[Content]{0}[Column1],
#"Added Custom" = Table.AddColumn(#"Reordered Columns", "Custom", each if [Home Team] = FullNameValue then "H" else "A"),