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

Matching delimited values in one column with delimited values in another column

Hi, 

 

I have data in the following format:

NameFile Location
Test 1a;b;c1;2;3
Test 2d4
Test 3e;f5;6

 

I'd like to expand the rows, so that each row contains a Name, File, Location match. For example:

NameFileLocation
Test 1a1
Test 1b2
Test 1c3
Test 2d4
Test 3e5
Test 3f6

 

It would be even better if this was possible:

NameFileLocation
Test 1.1a1
Test 1.2b2
Test 1.3c3
Test 2d4
Test 3.1e5
Test 3.2f6

 

Does anyone know how this can be done? Thank you all very much for your time. 

 

- Daniel

1 ACCEPTED SOLUTION
Stachu
Community Champion
Community Champion

I'd do it in the query editor, see example below (create a new query and paste the code from below in the Advanced Editor), you can examine the steps in the transformation in the Query Settings pane on the right of the Query Editor

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCkktLlEwVNJRSrROsk4G0obWRtbGSrE6UCkjoFAKEJsghIyB3FTrNCBpam2mFBsLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Name = _t, #"File " = _t, Location = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"File ", type text}, {"Location", type text}}),
    FileToList = Table.TransformColumns(#"Changed Type",{{"File ", each Text.SplitAny(_, ";")}}),
    LocationToList = Table.TransformColumns(FileToList,{{"Location", each Text.SplitAny(_, ";")}}),
    ZipFileAndLocation = Table.AddColumn(LocationToList, "Custom", each List.Zip({[#"File "],[Location]})),
    #"Removed Columns" = Table.RemoveColumns(ZipFileAndLocation,{"File ", "Location"}),
    #"Expanded Custom" = Table.ExpandListColumn(#"Removed Columns", "Custom"),
    #"Extracted Values" = Table.TransformColumns(#"Expanded Custom", {"Custom", each Text.Combine(List.Transform(_, Text.From), ";"), type text}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Extracted Values", "Custom", Splitter.SplitTextByEachDelimiter({";"}, QuoteStyle.Csv, false), {"File", "Location"})
in
    #"Split Column by Delimiter"


Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

Proud to be a Super User!

View solution in original post

2 REPLIES 2
Stachu
Community Champion
Community Champion

I'd do it in the query editor, see example below (create a new query and paste the code from below in the Advanced Editor), you can examine the steps in the transformation in the Query Settings pane on the right of the Query Editor

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCkktLlEwVNJRSrROsk4G0obWRtbGSrE6UCkjoFAKEJsghIyB3FTrNCBpam2mFBsLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Name = _t, #"File " = _t, Location = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"File ", type text}, {"Location", type text}}),
    FileToList = Table.TransformColumns(#"Changed Type",{{"File ", each Text.SplitAny(_, ";")}}),
    LocationToList = Table.TransformColumns(FileToList,{{"Location", each Text.SplitAny(_, ";")}}),
    ZipFileAndLocation = Table.AddColumn(LocationToList, "Custom", each List.Zip({[#"File "],[Location]})),
    #"Removed Columns" = Table.RemoveColumns(ZipFileAndLocation,{"File ", "Location"}),
    #"Expanded Custom" = Table.ExpandListColumn(#"Removed Columns", "Custom"),
    #"Extracted Values" = Table.TransformColumns(#"Expanded Custom", {"Custom", each Text.Combine(List.Transform(_, Text.From), ";"), type text}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Extracted Values", "Custom", Splitter.SplitTextByEachDelimiter({";"}, QuoteStyle.Csv, false), {"File", "Location"})
in
    #"Split Column by Delimiter"


Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

Proud to be a Super User!

View solution in original post

lidderdj
Frequent Visitor

That's brilliant. Thank you so much! 

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!