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"
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"
Anonymous this returns null on some rows as you did not mention what the code needs to
when c2 is null
and
lookup c2 by c3+c4-> lookup c1 by c2+c4 and there is no match
Every combination of c3 and c4 has a corresponding match of c2 and c3. There shouldn't be any nulls, because:
- If there is a value for c2, then there must not be a value for c3.
- If there is no value for c2, then there must be a c3.
- There is always a value for c4.
- For c5, if there is a value for c2, just use the value already in c1.
- For c5, if there is no value in c2, take the combination of c3 and c4 (always unique) and find the corresponding combination of c2 and c4 (also always unique and always exists). Use the c1 from this match for c5.
yes, there is no combination for D11(c2)Z14(c4), C2(c2)Y5(c4) and E15(c2)X1(c4) in the first table that you provided
Column1 Column2 Column3 Column4 Index Column1.1 8 D11 Z14 3 null 22 C2 Y5 4 null 943 E15 X1 6 null Sorry, the initial table should have been:
Column1 Column2 Column3 Column4 1 B3 X11 67 B3 X19 8 A3 X41 22 C2 Y5 2 A1 Y15 943 D3 Y5 378 C1 Z11 65 C1 Y3 1 B3 X19 33 B3 X11 68 C1 Y3 452 A1 Y15 8 C2 Y5 43 A3 X41 9 C1 Z11 67 D3 Y5 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.