Forum Discussion

hello_MTC's avatar
hello_MTC
Icon for Helper III rankHelper III
4 years ago
Solved

Create new column from existing columns

  From above table i want to create a new column(yellow color) and get the data from existing columns(orange color).   Priority should be as below: 1_ override_outcome 2_ final_recommendat...
Need Help
  • Vijay_A_Verma's avatar
    Vijay_A_Verma
    4 years ago

    Please paste the data alongwith column names where problem is

Vijay_A_Verma's avatar
Vijay_A_Verma
Icon for Most Valuable Professional rankMost Valuable Professional
4 years ago

1. Import data from Mysql into Power Query.

2. Open the Advanced Editor

Put following code after Source statement

#"Replaced Value" = Table.ReplaceValue(Source,"",null,Replacer.ReplaceValue,{"stander_outcome", "recomanded_outcome", "override_outcome", "final_rcommendation_outcome"}),
    #"Added Custom" = Table.AddColumn(#"Replaced Value", "latest", each List.RemoveNulls({[override_outcome]}&{[final_rcommendation_outcome]}&{[recomanded_outcome]}&{[stander_outcome]}){0})
in
    #"Added Custom"

 

hello_MTC's avatar
hello_MTC
Icon for Helper III rankHelper III
4 years ago

showing this error 

  • Vijay_A_Verma's avatar
    Vijay_A_Verma
    Icon for Most Valuable Professional rankMost Valuable Professional
    4 years ago

    Second line which starts with actdb_incident, need to have a comma at the end.

    In Replaces value line, Source should be replaced with actdb_incident_user_outcomes

    • hello_MTC's avatar
      hello_MTC
      Icon for Helper III rankHelper III
      4 years ago

      Not working.

      let
      Source = MySQL.Database("maketimecount-enc.cqc9l79hnudu.eu-west-2.rds.amazonaws.com", "actdb", [ReturnSingleDatabase=true]),
      actdb_incident_user_outcomes = Source{[Schema="actdb",Item="incident_user_outcomes"]}[Data]
      in
      actdb_incident_user_outcomes,

      #"Replaced Value" = Table.actdb_incident_user_outcomes(Source,"",null,Replacer.actdb_incident_user_outcomes,{"stander_outcome", "recomanded_outcome", "override_outcome", "final_rcommendation_outcome"}),
      #"Added Custom" = Table.AddColumn(#"Replaced Value", "latest", each List.RemoveNulls({[override_outcome]}&{[final_rcommendation_outcome]}&{[recomanded_outcome]}&{[stander_outcome]}){0})
      in
      #"Added Custom"

      • Vijay_A_Verma's avatar
        Vijay_A_Verma
        Icon for Most Valuable Professional rankMost Valuable Professional
        4 years ago

        It was not properly merged. Use below one

        let
Source = MySQL.Database("maketimecount-enc.cqc9l79hnudu.eu-west-2.rds.amazonaws.com", "actdb", [ReturnSingleDatabase=true]),
actdb_incident_user_outcomes = Source{[Schema="actdb",Item="incident_user_outcomes"]}[Data],
#"Replaced Value" = Table.actdb_incident_user_outcomes(actdb_incident_user_outcomes,"",null,Replacer.actdb_incident_user_outcomes,{"stander_outcome", "recomanded_outcome", "override_outcome", "final_rcommendation_outcome"}),
#"Added Custom" = Table.AddColumn(#"Replaced Value", "latest", each List.RemoveNulls({[override_outcome]}&{[final_rcommendation_outcome]}&{[recomanded_outcome]}&{[stander_outcome]}){0})
in
#"Added Custom"