admin管理员组文章数量:1345317
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"),
本文标签： excelReference a cell in a Power QueryStack Overflow
版权声明：本文标题：excel - Reference a cell in a Power Query - Stack Overflow 内容由网友自发贡献，该文观点仅代表作者本人， 转载请联系作者并注明出处：http://www.betaflare.com/web/1743788320a2539094.html， 本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌抄袭侵权/违法违规的内容，一经查实，本站将立刻删除。
发表评论