How to create new GROUP field ?

[Anonymous]

Hi All

My PBI FILE :-

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

Mt expected result shown in yellow :-

[v-yingjl · Accepted solution]

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.

[v-yingjl]

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.

[Anonymous]

Hi Ying

When i apply your acript into my actual pbi file . the result is wrong.

my pbi file , ref to Tab "Recode SAL_C"

https://www.dropbox.com/s/46wxf6wk6wb13g1/SI_TDS_V050.pbix?dl=0

Thank you for your help

Paul

[v-yingjl]

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

[Anonymous]

Hi Ying

Thank you for your help , yes you are correct.

paul

[mussaenda · Accepted solution]

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]

[v-yingjl · Accepted solution]

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.

[Anonymous]

Hi Ying

You are good,finally it work.

thank you very much.

Paul

[negi007]

@Anonymous Create a new column like below

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

[Anonymous]

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]

@Anonymous , Try a new column like

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

[Anonymous]

Hi Amit

Thank you for your help.

I get error msg :-

[amitchandak]

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

otherwise, let the system suggest

[Anonymous]

Hi Amit

not more error but return null.