Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi All,
Can someone please help to see if this is possible in the Power Query Custom Column, I am trying to get the result to show in the same row but not sure how to add a concatenation &"-"& so when we return the results the value will show this for Company Name1
Expected Output for Company Name1 in the Custom Colum=
"This is marked Yes due to no record in Column3" - "This is marked Yes due to no record in Column4"
I want to then continue the script to look at other Company Names which I have tried below -
if[ColumnName1] = "y"
and [ColumnName2] = "Company1" and
[ColumnName3] <> ""
then "This is marked Yes due to no record in Column3 for Company 1" (I would expect something like &" - "& around this section)
else
if [ColumnName1] = "y" and
[ColumnName2] = "Company1" and [ColumnName4 <> ""
then "This is marked Yes for yes due to no record in Column4 "
else
(This will then look at other company names)
if [ColumnName1] = "y" and
[ColumnName2] = "Company2" and [ColumnName56 <> ""
then "This is marked Yes due to no record in Column56 for Company2"
else null
I hope that makes sense
Solved! Go to Solution.
Hi @DataLife321
You can try the following code in advanced editor in power query.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wcs7PLUjMqzRU0lGKRMGxOnBJIzRJZDljZHE0fSbI4mhypsjiYLlYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Customer = _t, Column1 = _t, CompanySA = _t, CompanyMS = _t, CompanyFE = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Customer", type text}, {"Column1", type text}, {"CompanySA", type text}, {"CompanyMS", type text}, {"CompanyFE", type text}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each let
a=if [CompanySA]="Y" then "CompanySA" else "",
b=if [CompanyMS]="Y" then "CompanyMS" else "",
c=if [CompanyFE]="Y" then "CompanyFE" else ""
in if [Customer]="Company1"and [Column1]="Y" and a<>"" then a&" "&b&" "&c else if [Customer]<>"Company1"and [Column1]="Y" and a<>"" then a&" "&b else null)
in
#"Added Custom"
Output
Best Regards!
Yolo Zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @DataLife321,
I'd like to help you but provide sample data (as table) and also expected result please.
Customer | Column1 | CompanySA | CompanyMS | CompanyFE | CustomColumn Expectation |
Company1 | Y | Y | Y | Y | CompanySA - CompanyFE - CompanyMS |
Company2 | Y | Y | Y | Y | CompanySA - CompanyMS |
Company3 | Y | Y | Y | Y |
Expected Result should be shown above in the table - (I did this in a new column column measure in the Data view but prefer a custom column as I need more IF's) Happy to keep going if you can provide me a way to do it this way.
'Master Sheet - (Doc)'[Column1] = "y" &&'Master Sheet - (Doc)'[Customer] = "Company1" &&'Master Sheet - ( Doc)'[CompanySA] <> BLANK(),"CompanySA" & " " &
Hi @DataLife321
You can try the following code in advanced editor in power query.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wcs7PLUjMqzRU0lGKRMGxOnBJIzRJZDljZHE0fSbI4mhypsjiYLlYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Customer = _t, Column1 = _t, CompanySA = _t, CompanyMS = _t, CompanyFE = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Customer", type text}, {"Column1", type text}, {"CompanySA", type text}, {"CompanyMS", type text}, {"CompanyFE", type text}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each let
a=if [CompanySA]="Y" then "CompanySA" else "",
b=if [CompanyMS]="Y" then "CompanyMS" else "",
c=if [CompanyFE]="Y" then "CompanyFE" else ""
in if [Customer]="Company1"and [Column1]="Y" and a<>"" then a&" "&b&" "&c else if [Customer]<>"Company1"and [Column1]="Y" and a<>"" then a&" "&b else null)
in
#"Added Custom"
Output
Best Regards!
Yolo Zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Great stuff mate thank you.. Managed to use this to get it working appreciate it
@DataLife321, I'm sorry but I don't get the logic.
How you can achieve this if all column values are the same and there is no connection between Customer (Company1, Company2 and Company3) vs CompanySA, CompanyMS and CompanyFE
Are you sure that you provided correct sample data?
Try to explan as simple as possible please.