Forum Discussion
Need help writing a formula in POWERBI
I have a column with multiple mixed values (device), see below, I like to create a new column and extract only serial number that has BDxxxxx in the serial name. So in this example, I end up in a new column with only devices that have BDxxxxxx
How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done". Once you examined the code, replace the Source step with your own source.Examine your own code and find the Source= line (or your language equivalent). Include any transforms that convert your source into a usable table. Then appy the "Add column" step.
5 Replies
Replace your record separator (comma, it seems) with a pipe symbol | and then use the PATH functions to unpivot your data.
Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information or anything not related to the issue or question.
If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216
Please show the expected outcome based on the sample data you provided.
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523Thank you ibendlin, Here is what I put together. I need to create a column of "What I want" and grab all serial numbers that have "BD" as part of the serial number, and leave others blank
Device Serial Number What I want RX-0018 BD2333202 BD2333202 BD2333203, RX-0021 BD233320 RX_0011,RX-0123 let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCorQNTAwtFCK1YlWcnIxMjY2NjIwQuEZ6yiAFRkZgoWDIuKBGgx1QGKGRsZKsbEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Device Serial Number" = _t]), #"Added Custom" = Table.AddColumn(Source, "Result", each List.First(List.Select(Text.Split([Device Serial Number],","), each Text.Start(_,2)="BD"))) in #"Added Custom"How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done". Once you examined the code, replace the Source step with your own source.
