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
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!!!