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"
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
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"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"Same issue. Rows are being duplicated when the columns are expanded after the merge. Always the rows with indexes 13 and 16: