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

[lidderdj]

Hi, 

I have data in the following format:

Name	File	Location
Test 1	a;b;c	1;2;3
Test 2	d	4
Test 3	e;f	5;6

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

Name	File	Location
Test 1	a	1
Test 1	b	2
Test 1	c	3
Test 2	d	4
Test 3	e	5
Test 3	f	6

It would be even better if this was possible:

Name	File	Location
Test 1.1	a	1
Test 1.2	b	2
Test 1.3	c	3
Test 2	d	4
Test 3.1	e	5
Test 3.2	f	6

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

- Daniel

[Stachu · Accepted solution]

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"

[lidderdj]

That's brilliant. Thank you so much!