Forum Discussion
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
Hi Tito ,
You could create it as a Calculated column in the front end=
Group A =var Namecur = [Name] --Current Namevar grouphigh = COUNTROWS(FILTER(List, Table[Group] = "A" && List[Name] = Namecur ))returnif(grouphigh > 0, "Group A", "non Group A")
12 Replies
Hi Tito ,
You could create it as a Calculated column in the front end=
Group A =var Namecur = [Name] --Current Namevar grouphigh = COUNTROWS(FILTER(List, Table[Group] = "A" && List[Name] = Namecur ))returnif(grouphigh > 0, "Group A", "non Group A")Hello,
it worked. Thank you very much!
Best regards
TitoTito fantastic! If you could mark it as a Solution I'd appreciate it!
You already did, thank you so much 🙂
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.
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"
