how to make a new column like this

[tinye]

Table:

L-A

B

C

L-B

C

D

E

My expectation:

L-AL-A

L-AB

L-AC

L-BL-B

L-BC

L-BD

L-BE

[v-yalanwu-msft · Accepted solution]

Hi, @tinye ;

You could add conditional column in power query.

1. add conditional column.

if [Column1] = "Men"  or [Column1] = "Women"   then [Column1] else null

2.Then fill down it.

3.Then filter the rows(not "Men"  an "Women")

The final show:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8k3NU4rViVZyBJNOYDI8Pxcq6gwmXcCkK5h0A5PuSrGxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
    #"Added Conditional Column" = Table.AddColumn(#"Changed Type", "Custom", each if [Column1] = "Men"  or [Column1] = "Women"   then [Column1] else null),
    #"Filled Down" = Table.FillDown(#"Added Conditional Column",{"Custom"}),
    #"Filtered Rows" = Table.SelectRows(#"Filled Down", each [Column1] <> "Men" and [Column1] <> "Women")
in
    #"Filtered Rows"

Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[tinye]

Great! Works! Thanks so much!

[tinye]

Hi, 

Okay, I can have a more clear example.

I have a table which I can't edit it, but it has some categorize like:

Column 1

Men

A

B

Women

C

D

E

F

I want to split two columns like:

Column1    Column2

A                 Men

B                 Men

C                 Women

D                 Women

E                  Women

F                  Women

[Fowmy]

@tinye 

Van you explain the logic?