Creating a column with 2 others

[Shakaze]

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!

[rubayatyasmin]

@Shakaze 

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]}), "_")

[Shakaze]

@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".

[rubayatyasmin · Accepted solution]

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.