Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

undefined

Hey guys,

I need to create a column in my powerbi table (powerquery) with the following condition

If "brand" column is = "PEUGEOT" AND "CITROEN" then get the position from 1 to 3 
ELSE
position from 4 to 6.

Thanks

 

Need Help
  • jbwtp's avatar
    jbwtp
    3 years ago

    Hi Anonymous,

     

    Do you mean something like:

    Source = FatorequisicaoDetalhe

    Or if the table/query name has spaces or special characters:

    Source = #"FatoRequisicaoDetalhe"

    Kind regards,

    John

8 Replies

Replies have been turned off for this discussion
  • jbwtp's avatar
    jbwtp
    Memorable Member
    3 years ago

    Hi Anonymous,

     

    This can be done through the AddColumn menu in PBI Power Query Editor.

    This is not clear, however, what do you mean by "get the position from 1 to 3"?

     

    Cheers,

    John

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      I have a field called Chassis, in position 1 to 3 I can identify the Peugeot and Citroen models, in position 4 to 6 I have the other manufacturers, I could do this separately, but I would need to duplicate the field and apply this rule , the question is can I do this only once, with one condition?

      • jbwtp's avatar
        jbwtp
        Memorable Member
        3 years ago

        Hi Anonymous,

         

        Do you mind visualising what you are saying with a couple of screenshots or tables, so it was clear where do you start and what are you trying to achieve?

         

        Thanks,

        John

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

     

    Claro João, sem problemas. 
    Coluna "Marca_Ajustada" e Coluna Chassi, A coluna que preciso, precisa pegar as 3 primeiras posições do chassis,
    se a coluna "marca_ajustada" for equal a  Citroen ou Peugeot,
    se for diferente, então tome as posições 4 a 6

     

    • jbwtp's avatar
      jbwtp
      Memorable Member
      3 years ago

      Hola, 

       

      if I understand your idea correctly, you can do it simiar to this:

      let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wcs4sKcpPzVPSUTI0MjYxNYMwlGJ1opUCUkvTU/NLgCJgGXMIAyzlll+UAuSChS2ADBAJEg/Jr8wvSQQKgCUsgQwQGRsLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Marca = _t, Chassi = _t, Expected = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Marca", type text}, {"Chassi", type text}, {"Expected", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each if List.Contains({"CITROEN", "PEUGEOT"}, Text.Upper([ Marca])) then Text.Start([Chassi], 3) else Text.Range([Chassi], 3, 3), type text)
in
    #"Added Custom"

       

      Cheers,

      John

      • Anonymous's avatar
        Anonymous
        Not applicable
        3 years ago

        John, how would I point the source to my data table.

        Data Table = "FatoRequisicaoDetalhe"
        And in this structure that you gave as an example I create a new table, I need to include columns in the same table "FatoRequisicaoDetalhe"