Forum Discussion

Shakaze's avatar
Shakaze
Frequent Visitor
2 years ago
Solved

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...
Need Help
  • rubayatyasmin's avatar
    rubayatyasmin
    2 years ago

    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.

     

rubayatyasmin's avatar
rubayatyasmin
Community Champion
2 years ago

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's avatar
Shakaze
Frequent Visitor
2 years ago

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's avatar
    rubayatyasmin
    Community Champion
    2 years ago

    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.