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

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
non23
Helper I
Helper I

Create a new column based on another column

Hi guys.
Is there a way to create a new column for this one? Below is an example of what we want to achieve.
Value is the original column.

Screenshot 2024-08-19 154224.png

1 ACCEPTED SOLUTION
dufoq3
Super User
Super User

Hi @non23, check this:

 

Output

dufoq3_0-1724065810113.png

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjU0NDIw0NMzNbS0MDCwNrW0NANzLUFcpdhYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Value = _t]),
    Ad_NewColumn = Table.AddColumn(Source, "New Column", each 
        [ a = Text.Split([Value], ";"),
          b = List.Transform(a, (x)=> Text.Split(x, "..")),
          c = List.Transform(b, (x)=>  List.Generate( ()=> Number.From(x{0}), each _ <= Number.From(x{1}), each _ +100 ) ),
          d = List.Combine(c)
        ][d]),
    ExpandedNewColumn = Table.ExpandListColumn(Ad_NewColumn, "New Column")
in
    ExpandedNewColumn

Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

View solution in original post

4 REPLIES 4
dufoq3
Super User
Super User

Hi @non23, check this:

 

Output

dufoq3_0-1724065810113.png

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjU0NDIw0NMzNbS0MDCwNrW0NANzLUFcpdhYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Value = _t]),
    Ad_NewColumn = Table.AddColumn(Source, "New Column", each 
        [ a = Text.Split([Value], ";"),
          b = List.Transform(a, (x)=> Text.Split(x, "..")),
          c = List.Transform(b, (x)=>  List.Generate( ()=> Number.From(x{0}), each _ <= Number.From(x{1}), each _ +100 ) ),
          d = List.Combine(c)
        ][d]),
    ExpandedNewColumn = Table.ExpandListColumn(Ad_NewColumn, "New Column")
in
    ExpandedNewColumn

Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

Thank you @dufoq3. It works but there are some data in the column that doesn't have a list please see below sample:

Screenshot 2024-08-20 093809.png
May I know I should update the query please.

What should be the output of this? In 1st post you wanted numbers increased by 100.


Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

wdx223_Daniel
Super User
Super User

NewStep= Table.ExpandListColumn(Table.AddColumn(YourTable,"NewValue",each Expression.Evaluate("{"&Text.Replace([Value],";",",")&"}")),"NewValue")

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

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

July PBI25 Carousel

Power BI Monthly Update - July 2025

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