cancel
Showing results for
Did you mean:
Regular Visitor

## IF function - single value selcteded from a row

Hi

I Have a table with 3 Columns (A, B, C) that contain numbers between 1 - 10.

I need to create a new table (or add custom columns to the first table),

that check for the occurrence of each number in the left table. If yes show "1", else show "0".

The result table should look like the table on the right.

A   B  C            D   E  F   G   H   I    J   K  L  M

2   4   8             0   1   0   1   0   0   0   1   0   0

5   1   7             1   0   0   0   1   0   1   0   0   0

8   9   6             0   0   0   0   0   1   0   1   1   0

I can do it by adding custom column for each one of the  1-10 numbers as follow:

if [A] = 1 or [B] = 1 or [C] = 1 then 1 else 0

if [A] = 2 or [B] = 2 or [C] = 2 then 1 else 0 ,etc.

My question:

1. Is there a way (M or DAX) to use the if function to select a row instead of

using the 'or' operator for each column?

2. Is there a way to add these custom columns from a list or a function

Thanks for the help.

2 ACCEPTED SOLUTIONS

Accepted Solutions
Super Contributor

## Re: IF function - single value selcteded from a row

That is a nice and easy solution.

An alternative with some more programming, would be:

```let
Source = Excel.CurrentWorkbook(){[Name="Input"]}[Content],
Typed1 = Table.TransformColumnTypes(Source,{{"A", Int64.Type}, {"B", Int64.Type}, {"C", Int64.Type}}),
AddedLists = Table.AddColumn(Typed1, "Custom", (row) => List.Transform({1..10}, each if List.Contains(Record.FieldValues(row),_) then 1 else 0), type {Int64.Type}),
Tabled = Table.TransformColumns(AddedLists,{{"Custom", each Table.FromRows({_},{"D".."M"}), type table}}),
Expanded = Table.ExpandTableColumn(Tabled, "Custom", {"D".."M"}),
Typed2 = Table.TransformColumnTypes(Expanded,List.Transform({"D".."M"}, each {_, Int64.Type}))
in
Typed2```
Specializing in Power Query Formula Language (M)
Super User

## Re: IF function - single value selcteded from a row

Thank you @MarcelBeug

4 REPLIES 4
Highlighted
Super User

## Re: IF function - single value selcteded from a row

Hi,

Yes, it is possible to solve this in M.

```let
Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Number1", Int64.Type}, {"Number2", Int64.Type}, {"Number3", Int64.Type}}),
#"Expanded Custom" = Table.ExpandListColumn(#"Added Custom2", "Custom"),
#"Removed Columns" = Table.RemoveColumns(#"Expanded Custom",{"Start from", "End at"}),
#"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"Custom", "Number1", "Number2", "Number3"}),
#"Added Custom3" = Table.AddColumn(#"Reordered Columns", "Custom.1", each if [Number1]=[Custom] or [Number2]=[Custom] or [Number3]=[Custom] then 1 else 0),
#"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Added Custom3", {{"Custom", type text}}, "en-US"), List.Distinct(Table.TransformColumnTypes(#"Added Custom3", {{"Custom", type text}}, "en-US")[Custom]), "Custom", "Custom.1")
in
#"Pivoted Column"```

Hope this helps.

Super Contributor

## Re: IF function - single value selcteded from a row

That is a nice and easy solution.

An alternative with some more programming, would be:

```let
Source = Excel.CurrentWorkbook(){[Name="Input"]}[Content],
Typed1 = Table.TransformColumnTypes(Source,{{"A", Int64.Type}, {"B", Int64.Type}, {"C", Int64.Type}}),
AddedLists = Table.AddColumn(Typed1, "Custom", (row) => List.Transform({1..10}, each if List.Contains(Record.FieldValues(row),_) then 1 else 0), type {Int64.Type}),
Tabled = Table.TransformColumns(AddedLists,{{"Custom", each Table.FromRows({_},{"D".."M"}), type table}}),
Expanded = Table.ExpandTableColumn(Tabled, "Custom", {"D".."M"}),
Typed2 = Table.TransformColumnTypes(Expanded,List.Transform({"D".."M"}, each {_, Int64.Type}))
in
Typed2```
Specializing in Power Query Formula Language (M)
Super User

## Re: IF function - single value selcteded from a row

Thank you @MarcelBeug

Regular Visitor

## Re: IF function - single value selcteded from a row

Thanks very much guys.

Great help!!!

Announcements

#### Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

#### Power Platform Summit North America

Register by September 5 to save \$200

#### Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

#### Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 246 members 2,640 guests
Recent signins: