Forum Discussion

Tito's avatar
Tito
Helper IV
2 years ago
Solved

add new column in PowerQuery

Hello, I would like to add a new column in PowerQuery. If a customer has Group A, it should return "Group A", otherwise "non Group A". Best regards Tito Data:   Rsult      
Need Help
  • SamWiseOwl's avatar
    SamWiseOwl
    2 years ago

    Hi Tito ,

    You could create it as a Calculated column in the front end=

     

    Group A =
    var Namecur = [Name] --Current Name
    var grouphigh = COUNTROWS(FILTER(List, Table[Group] = "A" && List[Name] = Namecur ))
    return
    if(
       grouphigh > 0, "Group A", "non Group A")
SamWiseOwl's avatar
SamWiseOwl
Super User
2 years ago

Hi Tito 

If you have to do it in the query editor then you could created a filtered version of the table within the M.

Then use the merged filter to join the two tables.

When you expand it would put the Group A next to all rows with A:

 


let
#"Group Tables" = let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCs/MyclMzFXSUXJUitVB5juh8Z3BfN/ECrhaCBsi7lOanAjkuEAlisoy8+BmBCfmlqbmwLWFpeZlQvTFAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, Group = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"Group", type text}}),
Filtered = Table.SelectRows(#"Changed Type", each ([Group] = "A"))
in
#"Changed Type",
#"Merged Queries" = Table.NestedJoin(#"Group Tables", {"Name"}, #"Group Table A", {"Name"}, "Filtered", JoinKind.LeftOuter),
#"Expanded Filtered" = Table.ExpandTableColumn(#"Merged Queries", "Filtered", {"Group"}, {"Group.1"})
in
#"Expanded Filtered"