Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Be one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now

Reply
Anonymous
Not applicable

Custom Column with Complex Conditions

‎02-27-2020 08:23 AM

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:

Column1Column2Column3Column4
1 B3X11
67 B3X19
8 D11Z14
22 C2Y5
2 A1Y15
943 E15X1
378 C1Z11
65 C1Y3
1B3 X19
33B3 X11
68C1 Y3
452A1 Y15
8E4 Z44
43A3 X41
9C1 Z11
67D3 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:

Column1Column2Column3Column4Column5
1 B3X1133
67 B3X191
8 A3X4143
22 C2Y58
2 A1Y15452
943 D3Y567
378 C1Z119
65 C1Y368
1B3 X191
33B3 X1133
68C1 Y368
452A1 Y15452
8C2 Y58
43A3 X4143
9C1 Z119
67D3 Y567

 

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

Labels:
Message 1 of 13
1,511 Views
0
1 ACCEPTED SOLUTION
smpa01
Super User
Super User
In response to Anonymous

‎02-27-2020 03:46 PM

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"
Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs

View solution in original post

Message 13 of 13
1,435 Views
12 REPLIES 12
smpa01
Super User
Super User

‎02-27-2020 09:10 AM

 

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

 

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs
Message 3 of 13
1,463 Views
0
Anonymous
Not applicable
In response to smpa01

‎02-27-2020 09:23 AM

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

 

Message 4 of 13
1,454 Views
0
smpa01
Super User
Super User
In response to Anonymous

‎02-27-2020 09:36 AM

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
Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs
Message 5 of 13
1,442 Views
0
Anonymous
Not applicable
In response to smpa01

‎02-27-2020 11:38 AM

@smpa01 

Sorry, the initial table should have been:

Column1Column2Column3Column4
1 B3X11
67 B3X19
8 A3X41
22 C2Y5
2 A1Y15
943 D3Y5
378 C1Z11
65 C1Y3
1B3 X19
33B3 X11
68C1 Y3
452A1 Y15
8C2 Y5
43A3 X41
9C1 Z11
67D3 Y5
Message 6 of 13
1,403 Views
0
smpa01
Super User
Super User
In response to Anonymous

‎02-27-2020 11:41 AM

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

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs
Message 7 of 13
1,401 Views
0
Anonymous
Not applicable
In response to smpa01

‎02-27-2020 01:03 PM

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

Message 8 of 13
1,385 Views
0
smpa01
Super User
Super User
In response to Anonymous

‎02-27-2020 01:08 PM

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

swqa.PNG

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs
Message 9 of 13
1,383 Views
0
Anonymous
Not applicable
In response to smpa01

‎02-27-2020 01:15 PM

I think we're getting different tables returned. At the extract from html step I get:

Annotation 2020-02-27 141429.png

 

That's why I tried just removing the top 21 rows (22, but then deleted the promote headers step).

Message 10 of 13
1,377 Views
0
smpa01
Super User
Super User
In response to Anonymous

‎02-27-2020 01:49 PM

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"
Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs
Message 11 of 13
1,182 Views
0
Anonymous
Not applicable
In response to smpa01

‎02-27-2020 02:11 PM

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

Annotation 2020-02-27 150748.png

Annotation 2020-02-27 150819.png

Message 12 of 13
1,175 Views
0
smpa01
Super User
Super User
In response to Anonymous

‎02-27-2020 03:46 PM

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"
Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs
Message 13 of 13
1,436 Views
amitchandak
Super User
Super User

‎02-27-2020 08:29 AM

@ImkeF , can you help.

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview. ✨
️ November 12th-14th, 2024
 Online Event
Register Here
Message 2 of 13
1,483 Views
0

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.

ArunFabCon

Microsoft Fabric Community Conference 2025

Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.

December 2024

A Year in Review - December 2024

Find out what content was popular in the Fabric community during 2024.

View All