Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 Customer | Promo ID | VFP Customer |
A AHAMED LEBBE/MANSO | 222023 | Y |
A AHAMED LEBBE/MANSO | Y | |
A AHAMED LEBBE/MANSO | Y | |
AABID/MAHMMOUDMR | 222023 | Y |
AABID/MAHMMOUDMR | Y | |
AABID/MAHMMOUDMR | Y | |
AABID/MAHMMOUDMR | Y | |
AABID/MAHMMOUDMR | Y | |
AABOEN/STIANMR | N | |
AADITYA | N |
Solved! Go to Solution.
Hi @Sudheeshp ,
This is my easy-to-understand method.
1.Add a custom column to return 1 or 0.
2.Group by as follows.
3.Add a custom to return the result.
4.Expand the Tables in the column.
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.
Hi @Sudheeshp ,
This is my easy-to-understand method.
1.Add a custom column to return 1 or 0.
2.Group by as follows.
3.Add a custom to return the result.
4.Expand the Tables in the column.
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.
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
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.