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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
jiexika24
Frequent Visitor

Create a Custom Column based on multiple columns

Hello, I'm trying to create a custom column (column E) that evaluates columns A through D to find cells with a whole number that is greater than or equal to 0 and puts that number into column E. If the cells in columns A through D only contain 'null' values, then column E will return 'null'. Note: Columns A through D will only contain 1 number or say 'null' (no aggregation is needed).

 

Here's an example of my end goal:

 

Custom Column.png

 

I tried the following formulas that both returned 'Error' in all the cells within column E.

 

= let
columns = { [A], [B], [C], [D] },
concatenated = Text.Combine(List.Transform(columns, each if _ = null then "" else Text.From(_)), ""),
validNumbers = List.Select(columns, each _ <> null and Number.RoundDown(Number.From(_)) = Number.From(_) and Number.From(_) >= 0),
firstValidNumber = if List.Count(validNumbers) > 0 then List.First(validNumbers) else null
in
if concatenated = "" then null else firstValidNumber

 

= let
columns = { [A], [B], [C], [D] },
nonNullNumbers = List.Select(columns, each _ <> null and Number.RoundDown(_) = _ and _ >= 0)
in
if List.Count(nonNullNumbers) > 0 then List.First(nonNullNumbers) else null

 

Thanks for any support you can provide!

1 ACCEPTED SOLUTION

Hi @jiexika24  

Thanks fot your quick reply, you can refer to the following steps.

In power query, select all columns, then select 'Merge column' 

vxinruzhumsft_0-1716534581358.png

Then you can input the new column name

vxinruzhumsft_1-1716534625038.png

After merging, change the column type to whole number.

vxinruzhumsft_2-1716534686727.png

 

Output

vxinruzhumsft_3-1716534705513.png

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.

View solution in original post

5 REPLIES 5
v-xinruzhu-msft
Community Support
Community Support

Hi,

Thanks for the solutions @wdx223_Daniel  and @watkinnc  provided and i want to offer some more information for user to refer to.

hello @jiexika24 , based on your description, you can merge the columns directly, you can refer to the following code to advanced editor in power query.

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WUtJRsgRiEIrVAXMhyNAAlY/EM4JxTbHJEsMzxCFpohQbCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [A = _t, B = _t, C = _t, D = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"A", Int64.Type}, {"B", Int64.Type}, {"C", Int64.Type}, {"D", Int64.Type}}),
    #"Inserted Merged Column" = Table.AddColumn(#"Changed Type", "Merged", each Text.Combine({Text.From([A], "en-US"), Text.From([B], "en-US"), Text.From([C], "en-US"), Text.From([D], "en-US")}, ""), type text),
    #"Changed Type1" = Table.TransformColumnTypes(#"Inserted Merged Column",{{"Merged", Int64.Type}})
in
    #"Changed Type1"

 

Output

vxinruzhumsft_0-1716432735044.png

 

 

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.

Thank you @v-xinruzhu-msft ! I really appreciate your help and see how this would work, but I should have mentioned that the example table I provided above isn't my actual data (since mine has PII).

 

Would it be possible to rework the expression so that it's not linked to a Json file? I can plug in my variable names, since they are lengthy (e.g., [#"Trainer: First Last Name - Techniques"]).

 

Thanks for any additional support you can provide!

Hi @jiexika24  

Thanks fot your quick reply, you can refer to the following steps.

In power query, select all columns, then select 'Merge column' 

vxinruzhumsft_0-1716534581358.png

Then you can input the new column name

vxinruzhumsft_1-1716534625038.png

After merging, change the column type to whole number.

vxinruzhumsft_2-1716534686727.png

 

Output

vxinruzhumsft_3-1716534705513.png

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.

wdx223_Daniel
Super User
Super User

=List.RemoveNulls(yourlist){0}?

watkinnc
Super User
Super User

= [A] ?? [B] ?? [C] ?? [D] 

--Nate


I’m usually answering from my phone, which means the results are visualized only in my mind. You’ll need to use my answer to know that it works—but it will work!!

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors