Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
Mike282
Helper III
Helper III

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

Hi all 

 

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

 

Record IDParent IDString Parts
1 www.
21google.
32com

 

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?

1 ACCEPTED SOLUTION
PhilipTreacy
Super User
Super User

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.

 

urltable.png

 

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



Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.


Blog :: YouTube Channel :: Connect on Linkedin


Proud to be a Super User!


View solution in original post

6 REPLIES 6
PhilipTreacy
Super User
Super User

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.

 

urltable.png

 

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



Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.


Blog :: YouTube Channel :: Connect on Linkedin


Proud to be a Super User!


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
Super User
Super User

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
Super User
Super User

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

 



Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.


Blog :: YouTube Channel :: Connect on Linkedin


Proud to be a Super User!


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 IDParent IDString Parts
1 www.
21google.
32com
4 www.
54amazon.
65com
7 www.
87facebook.
98com

 

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.

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"

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.