Forum Discussion
Pviot column?
I have a table with two columns: Project and URL. There's up to three URL lines per project.
Is there a way to create three URL columns and for them to be populated so up to three URLs show on the same line according to their respective project? Thanks
Existing:
| Project | URL |
| A | https1 |
| A | https2 |
| B | https3 |
| C | https4 |
| C | https5 |
| C | https6 |
Desired:
| Project | URL1 | URL2 | URL3 |
| A | https1 | https2 | |
| B | https3 | ||
| C | https4 | https5 | https6 |
Hello! In Power Query, I added an index column that will start over again each time there is a change in Project name. I first sorted on Project so that it groups them together and will start the count over.
I then pivoted the Index column and had the URL apear as the values in with no aggregation:
You can rename the columns of 1, 2, 3 to make them be whatever you want.
Here is the M-Code:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUcooKSkwVIrVQfCMwDwnKM8YzHOG8kxQeKYoPDOl2FgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Project = _t, URL = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Project", type text}, {"URL", type text}}),
#"Sorted Rows" = Table.Sort(#"Changed Type",{{"Project", Order.Ascending}}),
#"Grouping" = Table.Group(#"Sorted Rows", {"Project"}, {{"Index", each Table.AddIndexColumn(_, "Index", 1, 1, Int64.Type), type table}}),
#"Index" = Table.ExpandTableColumn(#"Grouping", "Index", {"Index", "URL"}),
#"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(Index, {{"Index", type text}}, "en-US"), List.Distinct(Table.TransformColumnTypes(Index, {{"Index", type text}}, "en-US")[Index]), "Index", "URL")
in
#"Pivoted Column"
3 Replies
Hello! In Power Query, I added an index column that will start over again each time there is a change in Project name. I first sorted on Project so that it groups them together and will start the count over.
I then pivoted the Index column and had the URL apear as the values in with no aggregation:
You can rename the columns of 1, 2, 3 to make them be whatever you want.
Here is the M-Code:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUcooKSkwVIrVQfCMwDwnKM8YzHOG8kxQeKYoPDOl2FgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Project = _t, URL = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Project", type text}, {"URL", type text}}),
#"Sorted Rows" = Table.Sort(#"Changed Type",{{"Project", Order.Ascending}}),
#"Grouping" = Table.Group(#"Sorted Rows", {"Project"}, {{"Index", each Table.AddIndexColumn(_, "Index", 1, 1, Int64.Type), type table}}),
#"Index" = Table.ExpandTableColumn(#"Grouping", "Index", {"Index", "URL"}),
#"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(Index, {{"Index", type text}}, "en-US"), List.Distinct(Table.TransformColumnTypes(Index, {{"Index", type text}}, "en-US")[Index]), "Index", "URL")
in
#"Pivoted Column"Thanks audreygerred 😀
I've got a second 'issue' - do you know if its possible to hyperlink the values in the table so they open unique URLs? The URLs are contained in an identical table as the values. I'm guessing maybe something like a lookup? I've tried conditional formatting Power BI columns but that only links to first or last url in a table.
If you have the URLs in the data, in Power BI you can click on whatever field(s) you want to be clickable links, go to the Column tools ribbon that will appear, expand the Data category option and select Web URL.
