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
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Welcome Super Users.jpg

Super User Season 2

Congratulations, the new Super User Season 2 for 2021 has started!

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!