cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Impactful Individual
Impactful Individual

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

Paulyeo11_0-1604311018689.png

 

2 ACCEPTED SOLUTIONS

Hi @Paulyeo11 ,

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

group.png

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.

View solution in original post

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]



 

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

13 REPLIES 13
Community Support
Community Support

Hi @Paulyeo11 ,

You can create a column like this:

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

re.png

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.

Impactful Individual
Impactful Individual

Hi Ying

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

Paulyeo11_0-1604456810900.png

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

Hi @Paulyeo11 ,

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

Impactful Individual
Impactful Individual

Hi Ying

Thank you for your help , yes you are correct.

paul

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]



 

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

Hi @Paulyeo11 ,

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

group.png

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.

View solution in original post

Impactful Individual
Impactful Individual

Hi Ying

You are good,finally it work.

thank you very much.

Paul

Super User II
Super User II

@Paulyeo11 Create a new column like below

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

 

negi007_0-1604311871822.png

 

Impactful Individual
Impactful Individual

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
Super User IV
Super User IV

@Paulyeo11 , Try a new column like

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



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Hi Amit

Thank you for your help.

I get error msg :-

Paulyeo11_0-1604312172840.png

 

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

otherwise, let the system suggest



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Hi Amit

not more error but return null.

Paulyeo11_0-1604316966967.png

 

Helpful resources

Announcements
secondImage

Happy New Year from Power BI

This is a must watch for a message from Power BI!

December Update

Check it Out!

Click here to read more about the December 2020 Updates!

Community Blog

Check it Out!

Click here to read the latest blog and learn more about contributing to the Power BI blog!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors