Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
Sudheeshp
Frequent Visitor

New custom column

My table has column name 'Unique Customer' and 'Promo ID'. if any of the row value 'Unique Customer' has a 'Promo ID' greater than null, then likes to create a new column 'VFP Customer' with "Y" as value. as shown below.    The following VFP Customer is created manually.  

Unique CustomerPromo IDVFP Customer
A AHAMED LEBBE/MANSO222023Y
A AHAMED LEBBE/MANSO Y
A AHAMED LEBBE/MANSO Y
AABID/MAHMMOUDMR222023Y
AABID/MAHMMOUDMR Y
AABID/MAHMMOUDMR Y
AABID/MAHMMOUDMR Y
AABID/MAHMMOUDMR Y
AABOEN/STIANMR N
AADITYA N

 

1 ACCEPTED SOLUTION
v-stephen-msft
Community Support
Community Support

Hi @Sudheeshp ,

 

This is my easy-to-understand method.

1.Add a custom column to return 1 or 0.

vstephenmsft_0-1681464952089.png

vstephenmsft_1-1681464959414.png

2.Group by as follows.

vstephenmsft_2-1681464983939.png

vstephenmsft_5-1681465098638.png

vstephenmsft_7-1681465167287.png

3.Add a custom to return the result.

vstephenmsft_6-1681465159443.png

vstephenmsft_8-1681465180102.png

4.Expand the Tables in the column.

vstephenmsft_9-1681465204351.png

vstephenmsft_10-1681465210671.png

   

                                                                                                                                                         

Best Regards,

Stephen Tao

 

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

8 REPLIES 8
v-stephen-msft
Community Support
Community Support

Hi @Sudheeshp ,

 

This is my easy-to-understand method.

1.Add a custom column to return 1 or 0.

vstephenmsft_0-1681464952089.png

vstephenmsft_1-1681464959414.png

2.Group by as follows.

vstephenmsft_2-1681464983939.png

vstephenmsft_5-1681465098638.png

vstephenmsft_7-1681465167287.png

3.Add a custom to return the result.

vstephenmsft_6-1681465159443.png

vstephenmsft_8-1681465180102.png

4.Expand the Tables in the column.

vstephenmsft_9-1681465204351.png

vstephenmsft_10-1681465210671.png

   

                                                                                                                                                         

Best Regards,

Stephen Tao

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.           

 

Vijay_A_Verma
Super User
Super User

See the code here (You will need to replace Source appropriately)

let
    Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    ListNonBlanks = List.Buffer(List.Transform(List.Select(Table.ToRecords(Source), (x) => x[Promo ID] <> null), (x) => x[Unique Customer])),
    #"Added Custom" = Table.AddColumn(Source, "ID VFP Customer", each if List.Contains(ListNonBlanks, [Unique Customer]) then "Y" else "N")
in
    #"Added Custom"

 

I have to add as a custom column and apply this, is it??The following is my source!!

Folder.Files("C:\INVENTORY\YEAR\ALL FILES\RETAIL SALES\DAILY SALES\VFP ANALYSIS")

Open your file in PQ and in the code generated, copy the code ListNonBlanks till the end and paste into your code after Source. 

I am so thankful for all the reply.  Much much appreciated.  however i do not know how to do this additional column by comparing two cell values:)🤔

Well, I am getting errors.  I think the unique customer is a additional colu mn, and there are columns added in the power query, after importing

Vijay_A_Verma
Super User
Super User

You can create a custom column and name it VFP Customer. Put following formula (if you don't need null in else, please replace it appropriately)

= if [Promo ID] <> null then "Y" else null

 

I have pasted a scenario samples.  Please have a look

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel1

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

Top Solution Authors
Top Kudoed Authors