Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Change granularity

Hello, I am working on a project to analyse certain customer opportunities and I want to analyse which employees are available per opportunity. Right now I have a table "new_candidates" with employee...
Need Help
  • mitsu's avatar
    mitsu
    6 years ago

    Good Point  this can be handled by creating a custom column , with text . split function .  Refer the following M code for your reference . 

     Reference : https://www.youtube.com/watch?v=9krfJLv8ENk

    let
    Source = Excel.Workbook(File.Contents("...Documents\Smap.xlsx"), null, true),
    Table1_Table = Source{[Item="Table1",Kind="Table"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(Table1_Table,{{"tart date", type date}, {"Topic", type text}, {"Candidates", type text}, {"Description", type text}, {"cloud", Int64.Type}, {"probability", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Split Candidates", each Text.Split([Candidates],",")),
    #"Expanded Split Candidates" = Table.ExpandListColumn(#"Added Custom", "Split Candidates")
    in
    #"Expanded Split Candidates"

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Hi mitsu, this indeed worked, but isn't future proof. What happen when in future I have 4 possible candidates? then I would have to transform data again and select the 4th column as well?

mitsu's avatar
mitsu
Resolver IV
6 years ago

Good Point  this can be handled by creating a custom column , with text . split function .  Refer the following M code for your reference . 

 Reference : https://www.youtube.com/watch?v=9krfJLv8ENk

let
Source = Excel.Workbook(File.Contents("...Documents\Smap.xlsx"), null, true),
Table1_Table = Source{[Item="Table1",Kind="Table"]}[Data],
#"Changed Type" = Table.TransformColumnTypes(Table1_Table,{{"tart date", type date}, {"Topic", type text}, {"Candidates", type text}, {"Description", type text}, {"cloud", Int64.Type}, {"probability", Int64.Type}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Split Candidates", each Text.Split([Candidates],",")),
#"Expanded Split Candidates" = Table.ExpandListColumn(#"Added Custom", "Split Candidates")
in
#"Expanded Split Candidates"

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Cool, thanks alot!