Create DAX/ Power Query column from a column with different items

[Datagulf]

I want to create a column from another column. The column has Numerical values and Text Values hence can only be stored in text values. The column is as follows and the column next to it is my ask. 

Item Code		Desired Column
100000		Item
100001		Item
100002		Item
100003		Item
100004		Item
100005		Item
6000000		Parts
6000001		Parts
6000002		Parts
6000003		Parts
6000004		Parts
LABOUR		Labour
LABOUR		Labour

The items range is 100000 to 100117 and the Parts range is 6000000 to 6000234. LABOUR is just Labour.

[mangaus1111]

Hi @Datagulf ,

add a conditional column  using this code

#"Added Conditional Column" = Table.AddColumn(#"Removed Top Rows", "Custom", each if Text.StartsWith([#"Item Code "], "1") then "Item" else if Text.StartsWith([#"Item Code "], "6") then "Parts" else "Labour")

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Datagulf]

The startswith is not working for me. It  brings an error..

@mangaus1111 

[v-yinliw-msft · Accepted solution]

Hi @Datagulf ,

You can try this method:

New a column:

Desired Column =
IF (
    MID ( 'Table'[Item Code ], 1, 3 ) = "100",
    "Item",
    IF ( MID ( 'Table'[Item Code ], 1, 3 ) = "600", "Parts", "Labour" )
)

The result is:

Hope this helps you.

Here is my PBIX file.

Best Regards,

Community Support Team _Yinliw

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.