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.
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.
Solved! Go to Solution.
Hi @non23, check this:
Output
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
Hi @non23, check this:
Output
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
Thank you @dufoq3. It works but there are some data in the column that doesn't have a list please see below sample:
May I know I should update the query please.
NewStep= Table.ExpandListColumn(Table.AddColumn(YourTable,"NewValue",each Expression.Evaluate("{"&Text.Replace([Value],";",",")&"}")),"NewValue")