cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
dpbi Regular Visitor
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

instead of adding them one by one?

 

 

Thanks for the help.

 

 

2 ACCEPTED SOLUTIONS

Accepted Solutions
MarcelBeug Super Contributor
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)

View solution in original post

Highlighted
Super User
Super User

Re: IF function - single value selcteded from a row

4 REPLIES 4
Super User
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}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Start from", each 1),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "End at", each 10),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Custom", each { Number.From([Start from])..Number.From([End at]) }),
    #"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.

MarcelBeug Super Contributor
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)

View solution in original post

Highlighted
Super User
Super User

Re: IF function - single value selcteded from a row

dpbi Regular Visitor
Regular Visitor

Re: IF function - single value selcteded from a row

Thanks very much guys. 

Great help!!!

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

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

Users Online
Currently online: 78 members 1,318 guests
Please welcome our newest community members: