Forum Discussion
Solved
How to display two column data?
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
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.
2 Replies
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.
