Forum Discussion

dpbi's avatar
dpbi
Icon for Helper I rankHelper I
8 years ago
Solved

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 eac...
Need Help
  • MarcelBeug's avatar
    MarcelBeug
    8 years ago

    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
Ashish_Mathur's avatar
Ashish_Mathur
Icon for Super User rankSuper User
8 years ago

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's avatar
    MarcelBeug
    Icon for Community Champion rankCommunity Champion
    8 years ago

    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
      • dpbi's avatar
        dpbi
        Icon for Helper I rankHelper I
        8 years ago

        Thanks very much guys. 

        Great help!!!