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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
BonnieTseng
New Member

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

1 ACCEPTED SOLUTION
mussaenda
Super User
Super User

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"

 

2021_09_23_11_09_13_Untitled_Power_Query_Editor.png

 

Hope this helps.

View solution in original post

2 REPLIES 2
mussaenda
Super User
Super User

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"

 

2021_09_23_11_09_13_Untitled_Power_Query_Editor.png

 

Hope this helps.

Greg_Deckler
Super User
Super User

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

 

 


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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

Top Solution Authors
Top Kudoed Authors