Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
Tito
Advocate II
Advocate II

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:

Data.PNG

 

Rsult  

 

Result.PNG

1 ACCEPTED SOLUTION
SamWiseOwl
Resident Rockstar
Resident Rockstar

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

If you are happy with this answer please mark as a solution for others to find !

Kudos are always appreciated! Check out our free Power BI video courses.

View solution in original post

12 REPLIES 12
SamWiseOwl
Resident Rockstar
Resident Rockstar

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:

SamWiseOwl_0-1722863253686.png

 


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"


If you are happy with this answer please mark as a solution for others to find !

Kudos are always appreciated! Check out our free Power BI video courses.

SamWiseOwl
Resident Rockstar
Resident Rockstar

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

If you are happy with this answer please mark as a solution for others to find !

Kudos are always appreciated! Check out our free Power BI video courses.

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!

Hi @Tito 

Sadly calculated columns cannot be made dynamic.

They are only recalculated when the data is refreshed.


If you are happy with this answer please mark as a solution for others to find !

Kudos are always appreciated! Check out our free Power BI video courses.

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

Well Done!


If you are happy with this answer please mark as a solution for others to find !

Kudos are always appreciated! Check out our free Power BI video courses.

Hello @SamWiseOwl 

unfortunately does not work very well:

 

Problem Group.PNG

SamWiseOwl_0-1722947677449.png

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)

If you are happy with this answer please mark as a solution for others to find !

Kudos are always appreciated! Check out our free Power BI video courses.

Hello @SamWiseOwl 

perfect , it worked. Thank you very much.

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 )



Hello,

it worked. Thank you very much!

Best regards
Tito

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

 

You already did, thank you so much 🙂


If you are happy with this answer please mark as a solution for others to find !

Kudos are always appreciated! Check out our free Power BI video courses.

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.