Custom Column with Complex Conditions

[Anonymous]

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 it might be a little confusing. For this table:

Column1	Column2	Column3	Column4
1		B3	X11
67		B3	X19
8		D11	Z14
22		C2	Y5
2		A1	Y15
943		E15	X1
378		C1	Z11
65		C1	Y3
1	B3		X19
33	B3		X11
68	C1		Y3
452	A1		Y15
8	E4		Z44
43	A3		X41
9	C1		Z11
67	D3		Y5

I need a new column that gets the values from Column1. If there is a value in Column2, just use the value already in Column1. However, for rows with a value in Column3 (these rows can never have a value in Column2), take the combination of Column3 and Column4, find the matching combination of Column2 and Column4 and use the Column1 value for that matching row. Bear in mind, the combination of Column3 and Column4 will always be a unique value and the combination of Column2 and Column4 will always be a unique value. 

So the result would be something like this:

Column1	Column2	Column3	Column4	Column5
1		B3	X11	33
67		B3	X19	1
8		A3	X41	43
22		C2	Y5	8
2		A1	Y15	452
943		D3	Y5	67
378		C1	Z11	9
65		C1	Y3	68
1	B3		X19	1
33	B3		X11	33
68	C1		Y3	68
452	A1		Y15	452
8	C2		Y5	8
43	A3		X41	43
9	C1		Z11	9
67	D3		Y5	67

Does that make sense? Again, I have to do this in Power Query, not with DAX. Please help me find a way to make Column5!

Thanks

[smpa01]

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

[Anonymous]

@smpa01 

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.

[smpa01]

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

[Anonymous]

@smpa01 

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

[smpa01]

@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.

[Anonymous]

@smpa01 

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.

[smpa01]

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

[Anonymous]

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).

[smpa01]

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"

[Anonymous]

Same issue. Rows are being duplicated when the columns are expanded after the merge. Always the rows with indexes 13 and 16:

[smpa01 · Accepted solution]

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"

[amitchandak]

@ImkeF , can you help.