Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

New table with corresponding columns

Hi all,   I have a table like this:   And I want a table like this:   In the left, we have that lines with "VENDIDO" status, and "ENTREGUE" in the right. Attention to column matchin...
Need Help
  • Anonymous's avatar
    Anonymous
    7 years ago

    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.

v-cherch-msft's avatar
v-cherch-msft
Microsoft Employee
7 years ago

Hi Anonymous

 

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.

 

Regards,

Cherie

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    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 :(

     

    • tex628's avatar
      tex628
      Community Champion
      7 years ago

      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 :-)

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      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.

      • v-cherch-msft's avatar
        v-cherch-msft
        Microsoft Employee
        7 years ago

        Hi Anonymous

         

        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