Forum Discussion
Custom Column with Complex Conditions
Hi, I need some help with creating a custom column in Power Query. I need to do a lot with this column afterwards, so it can't be done with DAX, it has to be done in Power Query. The conditions for ...
should work
let Source = Web.BrowserContents("https://community.powerbi.com/t5/Desktop/Custom-Column-with-Complex-Conditions/td-p/951289"), #"Extracted Table From Html" = Html.Table(Source, {{"Column1", "TABLE:nth-child(7) > * > TR > :nth-child(1)"}, {"Column2", "TABLE:nth-child(7) > * > TR > :nth-child(2)"}, {"Column3", "TABLE:nth-child(7) > * > TR > :nth-child(3)"}, {"Column4", "TABLE:nth-child(7) > * > TR > :nth-child(4)"}, {"Column5", "TABLE:nth-child(7) > * > TR > :nth-child(5)"}}, [RowSelector="TABLE:nth-child(7) > * > TR"]), #"Promoted Headers" = Table.PromoteHeaders(#"Extracted Table From Html", [PromoteAllScalars=true]), #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Column1", Int64.Type}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", Int64.Type}}), #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1), #"Added Custom" = Table.AddColumn(#"Added Index", "Column 2 status", each if [Column2]="" then "blank" else "Not Blank"), #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom", each if [Column 2 status]="Not Blank" then "lookup c1 by c2" else "lookup c2 by c3+c4-> lookup c1 by c2+c4"), #"Merged Queries" = Table.NestedJoin(#"Added Custom1", {"Column3", "Column4"}, #"Added Custom1", {"Column2", "Column4"}, "Added Custom1", JoinKind.LeftOuter), #"Expanded Added Custom1" = Table.ExpandTableColumn(#"Merged Queries", "Added Custom1", {"Column2"}, {"Column2.1"}), #"Merged Queries1" = Table.NestedJoin(#"Expanded Added Custom1", {"Column2.1", "Column4"}, #"Expanded Added Custom1", {"Column2", "Column4"}, "Expanded Added Custom1", JoinKind.LeftOuter), #"Expanded Expanded Added Custom1" = Table.ExpandTableColumn(#"Merged Queries1", "Expanded Added Custom1", {"Column1"}, {"Column1.1"}), #"Filtered Rows" = Table.SelectRows(#"Expanded Expanded Added Custom1", each ([Custom] = "lookup c2 by c3+c4-> lookup c1 by c2+c4")), Custom1 = Table.SelectRows(#"Expanded Expanded Added Custom1", each ([Custom] <> "lookup c2 by c3+c4-> lookup c1 by c2+c4")), #"Removed Columns" = Table.RemoveColumns(Custom1,{"Column1.1"}), #"Added Custom2" = Table.AddColumn(#"Removed Columns", "Column1.1", each [Column1]), Custom2 = #"Filtered Rows"&#"Added Custom2", #"Sorted Rows" = Table.Sort(Custom2,{{"Index", Order.Ascending}}), #"Removed Other Columns" = Table.SelectColumns(#"Sorted Rows",{"Column1", "Column2", "Column3", "Column4", "Index", "Column1.1"}), #"Removed Duplicates" = Table.Distinct(#"Removed Other Columns", {"Index"}) in #"Removed Duplicates"
Anonymous No worries. My solution is still valid. Use the same code but change the data source to the new table in the first line.If you don't see what you expect to see please give me a buzz.
I ended up with a bunch of extra rows.
let
Source = Web.BrowserContents("https://community.powerbi.com/t5/Desktop/Custom-Column-with-Complex-Conditions/m-p/951289#M455818"),
#"Extracted Table From Html" = Html.Table(Source, {{"Column1", "TABLE:nth-child(3) > * > TR > :nth-child(1)"}, {"Column2", "TABLE:nth-child(3) > * > TR > :nth-child(2)"}, {"Column3", "TABLE:nth-child(3) > * > TR > :nth-child(3)"}, {"Column4", "TABLE:nth-child(3) > * > TR > :nth-child(4)"}}, [RowSelector="TABLE:nth-child(3) > * > TR"]),
#"Removed Top Rows" = Table.Skip(#"Extracted Table From Html",22),
#"Changed Type" = Table.TransformColumnTypes(#"Removed Top Rows",{{"Column1", Int64.Type}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}}),
#"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1),
#"Added Custom" = Table.AddColumn(#"Added Index", "Column 2 status", each if [Column2]="" then "blank" else "Not Blank"),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom", each if [Column 2 status]="Not Blank" then "lookup c1 by c2" else "lookup c2 by c3+c4-> lookup c1 by c2+c4"),
#"Merged Queries" = Table.NestedJoin(#"Added Custom1", {"Column3", "Column4"}, #"Added Custom1", {"Column2", "Column4"}, "Added Custom1", JoinKind.LeftOuter),
#"Expanded Added Custom1" = Table.ExpandTableColumn(#"Merged Queries", "Added Custom1", {"Column2"}, {"Column2.1"}),
#"Merged Queries1" = Table.NestedJoin(#"Expanded Added Custom1", {"Column2.1", "Column4"}, #"Expanded Added Custom1", {"Column2", "Column4"}, "Expanded Added Custom1", JoinKind.LeftOuter),
#"Expanded Expanded Added Custom1" = Table.ExpandTableColumn(#"Merged Queries1", "Expanded Added Custom1", {"Column1"}, {"Column1.1"}),
#"Filtered Rows" = Table.SelectRows(#"Expanded Expanded Added Custom1", each ([Custom] = "lookup c2 by c3+c4-> lookup c1 by c2+c4")),
Custom1 = Table.SelectRows(#"Expanded Expanded Added Custom1", each ([Custom] <> "lookup c2 by c3+c4-> lookup c1 by c2+c4")),
#"Removed Columns" = Table.RemoveColumns(Custom1,{"Column1.1"}),
#"Added Custom2" = Table.AddColumn(#"Removed Columns", "Column1.1", each [Column1]),
Custom2 = #"Filtered Rows"&#"Added Custom2",
#"Sorted Rows" = Table.Sort(Custom2,{{"Index", Order.Ascending}}),
#"Removed Other Columns" = Table.SelectColumns(#"Sorted Rows",{"Column1", "Column2", "Column3", "Column4", "Index", "Column1.1"})
in
#"Removed Other Columns"
I see the primary tactic, which is merging a table with itself (brilliant!), but now I can't figure out why I end up with so many extra rows.
with this
let Source = Web.BrowserContents("https://community.powerbi.com/t5/Desktop/Custom-Column-with-Complex-Conditions/m-p/951289#M455818"), #"Extracted Table From Html" = Html.Table(Source, {{"Column1", "TABLE:nth-child(3) > * > TR > :nth-child(1)"}, {"Column2", "TABLE:nth-child(3) > * > TR > :nth-child(2)"}, {"Column3", "TABLE:nth-child(3) > * > TR > :nth-child(3)"}, {"Column4", "TABLE:nth-child(3) > * > TR > :nth-child(4)"}}, [RowSelector="TABLE:nth-child(3) > * > TR"]), #"Promoted Headers" = Table.PromoteHeaders(#"Extracted Table From Html", [PromoteAllScalars=true]), #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Column1", Int64.Type}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}}), #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1), #"Added Custom" = Table.AddColumn(#"Added Index", "Column 2 status", each if [Column2]="" then "blank" else "Not Blank"), #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom", each if [Column 2 status]="Not Blank" then "lookup c1 by c2" else "lookup c2 by c3+c4-> lookup c1 by c2+c4"), #"Merged Queries" = Table.NestedJoin(#"Added Custom1", {"Column3", "Column4"}, #"Added Custom1", {"Column2", "Column4"}, "Added Custom1", JoinKind.LeftOuter), #"Expanded Added Custom1" = Table.ExpandTableColumn(#"Merged Queries", "Added Custom1", {"Column2"}, {"Column2.1"}), #"Merged Queries1" = Table.NestedJoin(#"Expanded Added Custom1", {"Column2.1", "Column4"}, #"Expanded Added Custom1", {"Column2", "Column4"}, "Expanded Added Custom1", JoinKind.LeftOuter), #"Expanded Expanded Added Custom1" = Table.ExpandTableColumn(#"Merged Queries1", "Expanded Added Custom1", {"Column1"}, {"Column1.1"}), #"Filtered Rows" = Table.SelectRows(#"Expanded Expanded Added Custom1", each ([Custom] = "lookup c2 by c3+c4-> lookup c1 by c2+c4")), Custom1 = Table.SelectRows(#"Expanded Expanded Added Custom1", each ([Custom] <> "lookup c2 by c3+c4-> lookup c1 by c2+c4")), #"Removed Columns" = Table.RemoveColumns(Custom1,{"Column1.1"}), #"Added Custom2" = Table.AddColumn(#"Removed Columns", "Column1.1", each [Column1]), Custom2 = #"Filtered Rows"&#"Added Custom2", #"Sorted Rows" = Table.Sort(Custom2,{{"Index", Order.Ascending}}), #"Removed Other Columns" = Table.SelectColumns(#"Sorted Rows",{"Column1", "Column2", "Column3", "Column4", "Index", "Column1.1"}) in #"Removed Other Columns"you are only ending up with one extra column which you can always kill
I think we're getting different tables returned. At the extract from html step I get:
That's why I tried just removing the top 21 rows (22, but then deleted the promote headers step).
please put this on an excel on your desktop, make a connection when you have everything up to Promoted headers
apply the following
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Column1", Int64.Type}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}}), #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1), #"Added Custom" = Table.AddColumn(#"Added Index", "Column 2 status", each if [Column2]="" then "blank" else "Not Blank"), #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom", each if [Column 2 status]="Not Blank" then "lookup c1 by c2" else "lookup c2 by c3+c4-> lookup c1 by c2+c4"), #"Merged Queries" = Table.NestedJoin(#"Added Custom1", {"Column3", "Column4"}, #"Added Custom1", {"Column2", "Column4"}, "Added Custom1", JoinKind.LeftOuter), #"Expanded Added Custom1" = Table.ExpandTableColumn(#"Merged Queries", "Added Custom1", {"Column2"}, {"Column2.1"}), #"Merged Queries1" = Table.NestedJoin(#"Expanded Added Custom1", {"Column2.1", "Column4"}, #"Expanded Added Custom1", {"Column2", "Column4"}, "Expanded Added Custom1", JoinKind.LeftOuter), #"Expanded Expanded Added Custom1" = Table.ExpandTableColumn(#"Merged Queries1", "Expanded Added Custom1", {"Column1"}, {"Column1.1"}), #"Filtered Rows" = Table.SelectRows(#"Expanded Expanded Added Custom1", each ([Custom] = "lookup c2 by c3+c4-> lookup c1 by c2+c4")), Custom1 = Table.SelectRows(#"Expanded Expanded Added Custom1", each ([Custom] <> "lookup c2 by c3+c4-> lookup c1 by c2+c4")), #"Removed Columns" = Table.RemoveColumns(Custom1,{"Column1.1"}), #"Added Custom2" = Table.AddColumn(#"Removed Columns", "Column1.1", each [Column1]), Custom2 = #"Filtered Rows"&#"Added Custom2", #"Sorted Rows" = Table.Sort(Custom2,{{"Index", Order.Ascending}}), #"Removed Other Columns" = Table.SelectColumns(#"Sorted Rows",{"Column1", "Column2", "Column3", "Column4", "Index", "Column1.1"}) in #"Removed Other Columns"