How to display two column data?

[BonnieTseng]

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

[mussaenda · Accepted 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.

[Greg_Deckler]

@BonnieTseng Try splitting your period on the : and then you can Merge the tables.