Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Dear all,
I have two sheet in PowerBI,
the one is indicator name ,
ex: 1, 2, 3, 4, 5 ......
the other one is indicator period,
ex:
1:Jan ,
1:Feb ,
1:July,
2:Jan,
2.Mar,
2.July
.......
how can i create the new column to show indicator period ( ex: 1: Jan+Feb+July , 2:Jan+Mar+July), thanks
Solved! Go to Solution.
Hi @BonnieTseng ,
@Greg_Deckler is right.
Try this on Blank Query:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMrTySsxT0FGK1QGx3VKT4Gyv0pxKCNsIpAbK1PNNLIIxQSqUYmMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
#"Split Column by Character Transition" = Table.SplitColumn(Source, "Column1", Splitter.SplitTextByCharacterTransition({"0".."9"}, (c) => not List.Contains({"0".."9"}, c)), {"Column1.1", "Column1.2"}),
#"Grouped Rows" = Table.Group(#"Split Column by Character Transition", {"Column1.1"}, {{"all", each _, type table [Column1.1=nullable text, Column1.2=nullable text]}}),
#"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.Column([all], "Column1.2")),
#"Extracted Values" = Table.TransformColumns(#"Added Custom", {"Custom", each Text.Select(Text.Combine(List.Transform(_, Text.From), "+"), {"A".."z", "+"}), type text}),
#"Inserted Merged Column" = Table.AddColumn(#"Extracted Values", "Output", each Text.Combine({[Column1.1], ": ", [Custom]}), type text),
#"Removed Columns" = Table.RemoveColumns(#"Inserted Merged Column",{"all", "Column1.1", "Custom"})
in
#"Removed Columns"
Hope this helps.
Hi @BonnieTseng ,
@Greg_Deckler is right.
Try this on Blank Query:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMrTySsxT0FGK1QGx3VKT4Gyv0pxKCNsIpAbK1PNNLIIxQSqUYmMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
#"Split Column by Character Transition" = Table.SplitColumn(Source, "Column1", Splitter.SplitTextByCharacterTransition({"0".."9"}, (c) => not List.Contains({"0".."9"}, c)), {"Column1.1", "Column1.2"}),
#"Grouped Rows" = Table.Group(#"Split Column by Character Transition", {"Column1.1"}, {{"all", each _, type table [Column1.1=nullable text, Column1.2=nullable text]}}),
#"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.Column([all], "Column1.2")),
#"Extracted Values" = Table.TransformColumns(#"Added Custom", {"Custom", each Text.Select(Text.Combine(List.Transform(_, Text.From), "+"), {"A".."z", "+"}), type text}),
#"Inserted Merged Column" = Table.AddColumn(#"Extracted Values", "Output", each Text.Combine({[Column1.1], ": ", [Custom]}), type text),
#"Removed Columns" = Table.RemoveColumns(#"Inserted Merged Column",{"all", "Column1.1", "Custom"})
in
#"Removed Columns"
Hope this helps.
@BonnieTseng Try splitting your period on the : and then you can Merge the tables.