Forum Discussion
Creating a column with 2 others
Hello everyone,
I'm working on a project about invoices.
I have 2 columns, one is the name the company A sending invoices, and the other the company recuiving the invoice: company B.
A --> B
But i also have the other way around : B --> A
My work is to check if A--> B and B --> A are the same.
I have created a column in power Query doing "A_B" and also "B_A"from both column.
What i'm struggling to create is to reunite "A_B" and "B_A" on the same group: "A_B".
I could do it for everyline with conditions but if in the future i'm adding new company, i'll have to do it again and again.
If anyone has any ideas! Thanks!
try creating a custom column, something like
= if Text.BeforeDelimiter([YourColumnName], "_") > Text.AfterDelimiter([YourColumnName], "_") then Text.AfterDelimiter([YourColumnName], "_") & "_" & Text.BeforeDelimiter([YourColumnName], "_") else [YourColumnName]
Replace [YourColumnName] with the name of your column that has the "A_B" or "B_A" values.
3 Replies
Before you create the "A_B" column, you should make sure that the two companies are sorted alphabetically. This will ensure that the concatenation is consistent.
For example in PQ editor,
Text.Combine(List.Sort({[CompanyA], [CompanyB]}), "_")
rubayatyasmin Yes it's already sorted alphabetically.
It's logical that i have both "A_B" and "B_A" because in the colum Company i have both A and B.
However what i'm willing to do is to reunite "A_B" and "B_A" to only "A_B".
try creating a custom column, something like
= if Text.BeforeDelimiter([YourColumnName], "_") > Text.AfterDelimiter([YourColumnName], "_") then Text.AfterDelimiter([YourColumnName], "_") & "_" & Text.BeforeDelimiter([YourColumnName], "_") else [YourColumnName]
Replace [YourColumnName] with the name of your column that has the "A_B" or "B_A" values.