Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

How to create new GROUP field ?

Hi All

 

My PBI FILE :-

https://www.dropbox.com/s/vsas6b4276pbwq4/GROUP%20V001.pbix?dl=0

 

Mt expected result shown in yellow :-

 

Need Help
  • v-yingjl's avatar
    v-yingjl
    5 years ago

    Hi Anonymous ,

    Try to create the new column like this:

    New Column_ =
IF (
    "G" & CONVERT ( [G_TYPE], STRING ) = [SAL_C]
        || [SAL_C] = BLANK (),
    "G" & CONVERT ( [G_TYPE], STRING ),
    [SAL_C]
)

    Attached the modified sample file in the below, hopes to help you.

     

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

  • mussaenda's avatar
    mussaenda
    5 years ago

    you can create a new column in Power Query to meet your conditions.

     

    if [YOUR GTYPE COLUMN] = null
or [YOUR GTYPE COLUMN] = ""
and ([YOUR SAL_C COLUMN] = null
or [YOUR SAL_C COLUMN] = "")
then null
else 

if [YOUR GTYPE COLUMN] = null
or [YOUR GTYPE COLUMN] = ""
and ([YOUR SAL_C COLUMN] <> null
or [YOUR SAL_C COLUMN] <> "")
then [YOUR SAL_C COLUMN]
else 

if [YOUR SAL_C COLUMN] = null
or [YOUR SAL_C COLUMN] = ""
then "G" & Number.ToText([YOUR GTYPE COLUMN])
else 
[YOUR SAL_C COLUMN]

     

     

13 Replies

  • negi007's avatar
    negi007
    Icon for Community Champion rankCommunity Champion
    5 years ago

    Anonymous Create a new column like below

    Group = "G"&'Type Group'[Type]

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Hi Negi

      Thank you for your reply.

      First take SAL_C value , in case it is missing , replace by GROUP_TYPE and Add G infront.

      SAL_C
      G1
      G3
       
      G9
      G2
  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    5 years ago

    Anonymous , Try a new column like

    New Column = calculate(firstnonblank([SAL_C], blaknk()), filter(Table, [G_type]= earlier([G_TYPE])))

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Hi Amit

      Thank you for your help.

      I get error msg :-

       

      • amitchandak's avatar
        amitchandak
        Icon for Super User rankSuper User
        5 years ago

        Anonymous , Table is keyword will be in a single quote.  'Table'

        otherwise, let the system suggest

  • v-yingjl's avatar
    v-yingjl
    Icon for Community Support rankCommunity Support
    5 years ago

    Hi Anonymous ,

    You can create a column like this:

    New Column =
CALCULATE (
    MAX ( 'TABLE'[SAL_C] ),
    FILTER ( ALL ( 'TABLE' ), 'TABLE'[G_TYPE] = EARLIER ( 'TABLE'[G_TYPE] ) )
)

    Attached the modified sample file in the below, hopes to help you.

     

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

      • v-yingjl's avatar
        v-yingjl
        Icon for Community Support rankCommunity Support
        5 years ago

        Hi Anonymous ,

        Based on the sample file, I want to comfirm some logic about this case(For example [G_Type = 1]):

        When [SAL_C] is blank or equals G1, the value of the new column should be G1, otherwise the value of the new column should be the same as [SAL_C].

        Is it right or anywhere wrong?

         

        Best Regards,
        Community Support Team _ Yingjie Li