Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
Hi guys,
I'm relatively new to Power BI and I have encountered an issue that I hope you can help me resolve.
I have several columns which contains rows that could look like below.
Row1: 401,402,403
Row2: 405,403,407,408
Row3: 403,405,404,410,406,402
Etc.
My issue is that I want to create new columns that either gives the value "1" if e.g. 401 is present in the row or extracts the value 401 to a new column that only contains the values 401 or "Null".
If I use the Extract or Split by delimiter function I will get a column containing the values 401, 405 and 403 in this example.
My data is connected to Dynamics through an API so the columns should be able to update accordingly.
I hope the above makes some sense. Thanks in advance guys.
Solved! Go to Solution.
Add the following formula to a customer column. It splits the line into a list based on a comma. List.Contains then tests to see if the value 401 is in the list.
if List.Contains(Text.Split([Line],","),"401") then 1 else null
Hi @Anonymous ,
Is that your excepted result?
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjEw1DExMAJiY6VYHRDfFMQGYnMgtoCKgfggcRMdE0MDIG0G0qMUGwsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
#"Added Custom" = Table.AddColumn(Source, "Custom", each if List.Contains(Text.Split([Column1],","),"401") then 1 else null)
in
#"Added Custom"
Hi @Anonymous ,
Is that your excepted result?
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjEw1DExMAJiY6VYHRDfFMQGYnMgtoCKgfggcRMdE0MDIG0G0qMUGwsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
#"Added Custom" = Table.AddColumn(Source, "Custom", each if List.Contains(Text.Split([Column1],","),"401") then 1 else null)
in
#"Added Custom"
Add the following formula to a customer column. It splits the line into a list based on a comma. List.Contains then tests to see if the value 401 is in the list.
if List.Contains(Text.Split([Line],","),"401") then 1 else null