add new column in PowerQuery

[Tito]

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  

 

[SamWiseOwl]

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"

[SamWiseOwl · Accepted solution]

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")

[Tito]

Hello @SamWiseOwl ,

can we make this calculated column dynamic. So if I select group A, it should return "group A", otherwise "non group A". And if I select group B, it should return "group B", otherwise "non group B" etc. 
Thanks!

[SamWiseOwl]

Hi @Tito 

Sadly calculated columns cannot be made dynamic.

They are only recalculated when the data is refreshed.

[Tito]

Hello @SamWiseOwl 
I have calculated it with a mesaure:

Dynamic Group =

VAR SelectedGroup = SELECTEDVALUE(GoupParameter[Group])

RETURN

IF(

    ISBLANK(SelectedGroup),

    BLANK(),

    IF(

        COUNTROWS(

            FILTER(

                Tabelle,

                Tabelle[Group] = SelectedGroup &&

                Tabelle[Name] = MAX(Tabelle[Name])

            )

        ) > 0,

        SelectedGroup,

        "non " & SelectedGroup

    )

)

[SamWiseOwl]

Well Done!

[Tito]

Hello @SamWiseOwl 

unfortunately does not work very well:

 

[SamWiseOwl]

Dynamic Group =

var Namecur = SELECTEDVALUE('Group'[Please choose group])--Current Name

VAR SelGroup = SELECTEDVALUE('Group'[Please choose group])

var grouphigh =

CALCULATE(COUNTROWS(List),all(List[Group]), List[Group] = SelGroup)

return

if(

   grouphigh > 0, "Group " & Namecur, "non Group "& Namecur)

[Tito]

Hello @SamWiseOwl 

perfect , it worked. Thank you very much.

[Tito]

Hello @SamWiseOwl 

I tried it, but it didn't work.

Group dynamic =

var _Group = SELECTEDVALUE(Tabelle[Group])

var Namecur = [Name] --Current Name

var grouphigh = COUNTROWS(FILTER(Tabelle, Tabelle[Group] = _Group && Tabelle[Name] = Namecur ))

return

if(

   grouphigh > 0, "Group " & _Group, "non Group " & _Group )

[Tito]

Hello,

it worked. Thank you very much!

Best regards
Tito

[SamWiseOwl]

@Tito  fantastic! If you could mark it as a Solution I'd appreciate it!

You already did, thank you so much 🙂