Forum Discussion
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.
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 Typed2Thank you MarcelBeug
4 Replies
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.
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 Typed2Thank you MarcelBeug