cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper I
Helper I

New table with corresponding columns

Hi all,

 

I have a table like this:

POWERBI1.png

 

And I want a table like this:

POWERBI2.png

 

In the left, we have that lines with "VENDIDO" status, and "ENTREGUE" in the right. Attention to column matching in "Data MD", "Bilhete", "Name" AND "Tipo de Venda" are equals; "Agência" is not necesary to coincide.

 

I have no idea how to do this in dax. Maybe a SUMMARIZE table with addons?

 

There is a minor error in the image above, in the right table, "ANDREA, ENTREGUE" should be "15/10/2018" as in the first table, sorry.

 

Anyone could help?

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Helper I
Helper I

Re: New table with corresponding columns

i GOT IT!

 

With SELECTCOLUMNS table above, I hade created new columns using LOOKUPVALUE.

 

'DVENDAS" is the main table.

 

Data Entregue = LOOKUPVALUE('DVENDAS'[Data MD];
'DVENDAS'[Bilhete];[Bilhete];
'DVENDAS'[Passageiro];[Passageiro];
'DVENDAS'[Data Servi.];[Data do serviço];
'DVENDAS'[Serviço];[Serviço];
'DVENDAS'[Status];"ENTREGUE")

Agência Entregue = LOOKUPVALUE('DVENDAS'[Agência];
'DVENDAS'[Bilhete];[Bilhete];
'DVENDAS'[Passageiro];[Passageiro];
'DVENDAS'[Data Servi.];[Data do serviço];
'DVENDAS'[Serviço];[Serviço];
'DVENDAS'[Status];"ENTREGUE")

 

Thanks guys for support.

View solution in original post

5 REPLIES 5
Highlighted
Microsoft
Microsoft

Re: New table with corresponding columns

Hi @alexandrecr

 

It seems you may try to use SELECTCOLUMNS Function with condition as requested. As i'm not clear about the condition, show a sample as below for your reference.

1.png

 

Regards,

Cherie

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted
Helper I
Helper I

Re: New table with corresponding columns

Actually, my original table is huge, millions of rows and more than 20 columns.

 

Two of this columns are called "Type of sale" and "Status".

 

I need a new table with [Type of sale]="PTA" && [Status]="VENDIDO". Note: vendido is the word in portuguese for sold.

 

In [Status] there is two relevant values: "VENDIDO" e "ENTREGUE" - delivered in english.

 

So I have this:

 

PTA = 
    SELECTCOLUMNS (
        FILTER (
            DVENDAS; [Tipo Venda]="PTA" && [Status]="VENDIDO");
            "Bilhete"; DVENDAS[Bilhete];
            "Agência"; DVENDAS[Agência];
            "Bilheteiro"; DVENDAS[Logins.nome];
            "Data da compra"; DVENDAS[Data MD];
            "Linha"; DVENDAS[Linha];
            "Serviço"; DVENDAS[Serviço];
            "Passageiro"; DVENDAS[Passageiro];
            "Data do serviço"; DVENDAS[Data Servi.];
            "Valor cobrado"; DVENDAS[Vlr.Cobrado])

But now I need to insert new columns in this table that shows me when this ticket was delivered (with [Status]="ENTREGUE", near to this columns, into right - and idk how to do 😞

 

Highlighted
Super User II
Super User II

Re: New table with corresponding columns

Yo mate, i believe that it's quite complicated to achieve what ur aiming for but i'll try and assist.

 

Due to the datamodel i believe that this is something that needs to be done in Power Query. 

 

This is what im thinking you should do:

Start by duplicating your initial query.

 

Next up, make two queries. one should hold the "VENDIDO" status, and one should hold "ENTREGUE" status. And of course all other fields that are relevant. 

 

The final step is a merge between the two queries. This requires that there is one key column that exists in both queries that connects the specific rows to eachother. It's also important that the relevant columns are renamed so that they arent merged together but stay divided. 

 

Hope tyhis gives a little help 🙂


Connect on LinkedIn
Highlighted
Helper I
Helper I

Re: New table with corresponding columns

i GOT IT!

 

With SELECTCOLUMNS table above, I hade created new columns using LOOKUPVALUE.

 

'DVENDAS" is the main table.

 

Data Entregue = LOOKUPVALUE('DVENDAS'[Data MD];
'DVENDAS'[Bilhete];[Bilhete];
'DVENDAS'[Passageiro];[Passageiro];
'DVENDAS'[Data Servi.];[Data do serviço];
'DVENDAS'[Serviço];[Serviço];
'DVENDAS'[Status];"ENTREGUE")

Agência Entregue = LOOKUPVALUE('DVENDAS'[Agência];
'DVENDAS'[Bilhete];[Bilhete];
'DVENDAS'[Passageiro];[Passageiro];
'DVENDAS'[Data Servi.];[Data do serviço];
'DVENDAS'[Serviço];[Serviço];
'DVENDAS'[Status];"ENTREGUE")

 

Thanks guys for support.

View solution in original post

Highlighted
Microsoft
Microsoft

Re: New table with corresponding columns

Hi @alexandrecr

 

Glad to hear you've solved it, please accept your answer as solution, that way, other community members will easily find the solution when they get same issue. 

 

Regards,

Cherie

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements

August Community Highlights

Check out a full recap of the month!

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

Top Solution Authors