cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
orforf Frequent Visitor
Frequent Visitor

Split two related colon separated columns into rows

how would I go about splitting these 2 columns into rows and keeping the relation intact

 

the relation being the order that they appear are linked to each other

 

 

Capture.JPG

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Split two related colon separated columns into rows

Hi,

@ChrisHaas has solved a similar problem here.  He should be able to help you with this one as well.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

6 REPLIES 6
Super User
Super User

Re: Split two related colon separated columns into rows

Hi,

Share the link from where I can download your PBI file.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
orforf Frequent Visitor
Frequent Visitor

Re: Split two related colon separated columns into rows

Hi Ashish

 

You can download the data here https://pastebin.com/E2RfFPzC

Super User
Super User

Re: Split two related colon separated columns into rows

Hi,

I do not know what to paste from there.  Please share data in 2 columns of an MS Excel file.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Community Support Team
Community Support Team

Re: Split two related colon separated columns into rows

@orforf 

 

You may refer to the code below.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSrROsk5W0lEytDayNlaK1YlWSrFOBfJNrE2VYmMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t]),
    #"Added Custom" = Table.AddColumn(Source, "Custom", each Table.FromColumns({Text.Split([Column1], ";"),Text.Split([Column2], ";")})),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Column1", "Column2"}, {"Custom.Column1", "Custom.Column2"})
in
    #"Expanded Custom"

 

Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
orforf Frequent Visitor
Frequent Visitor

Re: Split two related colon separated columns into rows

Hi @v-chuncz-msft 

@Ashish_Mathur 

 

Could you please show me how to apply it to the following file which contains the dataset.

 

https://1drv.ms/u/s!ArcejqUhGxP2iW_UKWT-6z3gRGGx

 

 

Thanks,

Ryan

Super User
Super User

Re: Split two related colon separated columns into rows

Hi,

@ChrisHaas has solved a similar problem here.  He should be able to help you with this one as well.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

Helpful resources

Announcements
New Ranks and Rank Icons in 2020

New Ranks and Rank Icons in 2020

Read the announcement for more information!

New Kudos Given Badges Coming

New Kudos Given Badges Coming

We're rolling out new Kudos Given badges. Find out how many Kudos you've given.

November 2019 Community Highlights

November 2019 Community Highlights

Get an overview of the events and great community content from November.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Solution Authors
Top Kudoed Authors (Last 30 Days)