Is there a way to join URL parts together if they're in separate rows

[Mike282]

Hi all 

So I've got a table with data that looks like this:

Record ID	Parent ID	String Parts
1		www.
2	1	google.
3	2	com

Each row has a record ID, a string part which contains a partial url that comes together to a full url if we join them together by the parent ID.

So for the example above I've got 3 rows which needs to be joined together by the parent ID key to form 

www.google.com

Is there a way to do this in M query?

[PhilipTreacy · Accepted solution]

Hi @Mike282 

Download sample PBIX with the following code/example

Not sure what format you want the final table to be in but this query does what you want without using recursive functions.

Bascially if the Parent ID is null, it's the start of a new url.  Add a comma to the beginning of the root part of the URL (in a Custom Column) then you can then concatenate the entire String Parts column.

Split that column by the comma delimiter you just inserted between URL's.  Remove unwanted rows and columns, and transpose the table.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUVIA4vLycj2lWJ1oJSMgBySYnp+fnpMKETMG8kHiyfm5YL4JuiZTIAckmJibWJWfBxEzA/JNkTSZo2uyAHJAgmmJyalJ+fnZEFFLoIgFTFssAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Record ID" = _t, #"Parent ID" = _t, #"String Parts" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Record ID", Int64.Type}, {"Parent ID", Int64.Type}, {"String Parts", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each if [Parent ID] is null then "," & [String Parts] else [String Parts]),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each Text.Combine(#"Added Custom"[Custom])),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Added Custom1", "Custom.1", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Custom.1.1", "Custom.1.2", "Custom.1.3", "Custom.1.4"}),
    #"Kept First Rows" = Table.FirstN(#"Split Column by Delimiter",1),
    #"Removed Columns" = Table.RemoveColumns(#"Kept First Rows",{"Record ID", "Parent ID", "String Parts", "Custom", "Custom.1.1"}),
    #"Transposed Table" = Table.Transpose(#"Removed Columns"),
    #"Renamed Columns" = Table.RenameColumns(#"Transposed Table",{{"Column1", "URL"}})
in
    #"Renamed Columns"

Regards

Phil

[AlexisOlson]

This is an interesting approach. I don't think you need new columns and transpose though after #"Added Custom".

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUVIA4vLycj2lWJ1oJSMgBySYnp+fnpMKETMG8kHiyfm5YL4JuiZTIAckmJibWJWfBxEzA/JNkTSZo2uyAHJAgmmJyalJ+fnZEFFLoIgFTFssAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Record ID" = _t, #"Parent ID" = _t, #"String Parts" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Record ID", Int64.Type}, {"Parent ID", Int64.Type}, {"String Parts", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each if [Parent ID] is null then "," & [String Parts] else [String Parts]),
    #"Combine and Split" = List.Select(Text.Split(Text.Combine(#"Added Custom"[Custom]),","), each Text.Length(_) > 0),
    #"Converted to Table" = Table.FromList(#"Combine and Split", null, {"URL"})
in
    #"Converted to Table"

[AlexisOlson]

With just a single address, you can write

Text.Combine(Source[String Parts])

It's not clear to me what your broader situation is. What does your data look like in a slightly less simple example?

[PhilipTreacy]

Hi @Mike282 

I can think of 2 or 3 ways to do this but the final solution will depend on what your entire dataset looks like.  With this small example you could transpose the table and then join the rows to get your URL, or you could pick out the url elements from the column using the row index, or you could do it with pivoting.

Can you please supply more data that accurately represents your real world scenario and an example of exactly what result you want.

Regards

Phil

[Mike282]

Hey all,

Text.combine won't work as it requires me to group rows together. I've added some more sample data as a example:

Record ID	Parent ID	String Parts
1		www.
2	1	google.
3	2	com
4		www.
5	4	amazon.
6	5	com
7		www.
8	7	facebook.
9	8	com

So the format is that the data all comes from one entity/table. A row represents a part of a URL. Each row has a unique ID/key (the record ID). Each row could also have a parent ID value, which basically joins to the previous part of the URL. For example above you have a record with a url part of www. and a ID of 1. As www. is the first part of the URL it does not have a parent ID. The second part of the URL google. with a record ID of 2 is the second part of the url format and proceeds after "www.", therefore it has a parent ID of 1. Likewise "com" has a parent ID of 2 joining it to "google." Together they would form the complete URL of "www.google.com".

Likewise I added a few more examples of rows that combine to form a complete URL path.

I'm trying to find a method of joining the URL parts together into the complete URL.

[AlexisOlson]

The tricky part here is dividing the table up into chunks for each complete URL. Luckily, we can define recursive functions in M, so let's first write a function that finds the root ID for any given row.

Define a function named Root by creating a new query with this name and paste this into the advanced query editor:

(n as number, Child as list, Parent as list) as number =>
let
     p = Parent{List.PositionOf(Child,n)}
in
     if p = null then n else Root(p, Child, Parent)

Now that this is defined, we can create a new custom column that calls it:

Root([Record ID], Source[Record ID], Source[Parent ID])

Finally, we can use Text.Combine, as I initially suggested, in a Group By. Here's the full query:

let
    SourceRaw = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUVIA4vLycj2lWJ1oJSMgBySYnp+fnpMKETMG8kHiyfm5YL4JQhOYbwpkg8Qgqs2ALJBIYm5iVX4eWMgcyDWDK7AAssyRjLNEd4OhAZAHEk1LTE5Nys/PhgqD3AWWA+uMBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Record ID" = _t, #"Parent ID" = _t, #"String Parts" = _t]),
    Source = Table.TransformColumnTypes(SourceRaw,{{"Record ID", Int64.Type}, {"Parent ID", Int64.Type}, {"String Parts", type text}}),
    #"Added Custom" = Table.AddColumn(Source, "Root", each Root([Record ID], Source[Record ID], Source[Parent ID]), Int64.Type),
    #"Grouped Rows" = Table.Group(#"Added Custom", {"Root"}, {{"URL", each Text.Combine([String Parts]), type text}})
in
    #"Grouped Rows"