How to make URL functionality work if values are comma/colon separated values.

[Patv]

Hi All, 

 I have a requirement where I have to make comma seperated values an individual URL.

e.g. I have two columns request id and  prog_manager_TEAMS_URL. ( please see ataches screen shot). If you notice there are three semicolum separated values ( assume that three program managers) for request id = 330173. I want to develope a functionality where I should be able to click on each URL separately ( rather than an entire value as a single URL as it doesn't open teams app) so it opens up separete Microsoft Team's chat room for each Program manager.  How can I make each URL separatly clickable.  Thank you in advance!

To get better idea, I have attached another screen shot where you can see if I hover my mouse on Prog_manager values, it shows entire URL ( which has all 3 prog mangers). I want to see separete URL for each prog manager so when I click on each prog Manager, a separate microsoft teams chat window opens up.

[BA_Pete · Accepted solution]

Hi @Patv ,

No worries. Here’s the steps in answer to your questions:

1) You create these two lists as new columns. Create a new custom column, call it ‘progMgrList’, and put this in as the calculation:

Text.Split([PROG_MANAGER], ";")

Do the same again for a new column called ‘progMgrUrlList’ with this calculation:

Text.Split([PROG_MANAGERS_TEAMS_URL], ";")

2) Again, add another new custom column, call it ‘listZip’, and enter this as the calculation:

List.Zip({[progMgrList], [progMgrUrlList]})

3) At the right of the column header of your new [listZip] column, there is a button which looks like two arrows turning away from each other. Click this and choose ‘Expand to New Rows’ from the dropdown options.

4) Hit the same button again, but, this time, choose the ‘Extract Values’ option from the dropdown. When prompted to choose a delimiter, select ‘Custom’ from the dropdown and type “|“ (pipe character) into the input field. You don’t have to use the pipe character, I just used this as it’s very unlikely that it’s used in your usernames, and never used in URLs.

5) Select your [listZip] column that now contains usernames concatenated with their URLs. Go to Transform tab > Split Column > By Delimiter. Again, when choosing the delimiter to split by, go to Custom then enter the pipe character into the input box.

Pete

[BA_Pete]

Hi @Patv ,

You need to split these URLs out into their own data points, as Power BI sees anything in a single cell as one piece of information (except maybe nested objects, but I digress).

In Power Query, I would split [PROG_MANAGERS] column by 'semicolon and space' ( '; ' ), then unpivot the resulting columns.

This will give you a list of program manager URLs by [REQUEST_ID].

Without seeing your actual data I can't be much more precise than this, but the principle is sound.

Pete

[Patv]

Thank you Pete for responding to my question. 

  If  I split [PROG_MANAGERS] column by 'semicolon and space' ( '; ' ), then unpivot the resulting columns.

This will give me a list of program manager URLs by [REQUEST_ID] as below  

Request id	Program Manager
330173	Dana Murphy
330173	Ann Bertrand
330173	Dipak Kothari

But my requirement is to have reuquest id and  all three program managers in a single line.

could you please also share power bi pivot and unpivot code if possible?

Thank you.

[BA_Pete]

Hi @Patv ,

Probably best if you can send me an example of your original data so I can put together the code steps for you.

Make sure to remove any sensitive data (just put XXX's in URLS etc.).

Pete

[Patv]

REQUEST_ID	PROG_MANAGER	PROG_MANAGERS_TEAMS_URL
324395	user1	https://teams.microsoft.com/l/chat/0/0?users=user1@xxx.com
330279	user1; user2;user3	https://teams.microsoft.com/l/chat/0/0?users=user1@xxx.com; https://teams.microsoft.com/l/chat/0/0?users=user2@xxx.com; https://teams.microsoft.com/l/chat/0/0?users=user3@xxx.com
318328	user1	https://teams.microsoft.com/l/chat/0/0?users=user1@xxx.com
318264	user1; user2	https://teams.microsoft.com/l/chat/0/0?users=user1@xxx.com; https://teams.microsoft.com/l/chat/0/0?users=user2@xxx.com
321050	user1	https://teams.microsoft.com/l/chat/0/0?users=user1@user1.com
333089	user1; user2; user3; user4; user5; user6; user7;user8; user9; user10	https://teams.microsoft.com/l/chat/0/0?users=user1@xxx.com; https://teams.microsoft.com/l/chat/0/0?users=user2@xxx.com; https://teams.microsoft.com/l/chat/0/0?users=user3@xxx.com; https://teams.microsoft.com/l/chat/0/0?users=user4@xxx.com; https://teams.microsoft.com/l/chat/0/0?users=user5@xxx.com; https://teams.microsoft.com/l/chat/0/0?users=user6@xxx.com; https://teams.microsoft.com/l/chat/0/0?users=user7@xxx.com; https://teams.microsoft.com/l/chat/0/0?users=user8@xxx.com; https://teams.microsoft.com/l/chat/0/0?users=user9@xxx.com; https://teams.microsoft.com/l/chat/0/0?users=user10@xxx.com
340880	user1;user2; user3	https://teams.microsoft.com/l/chat/0/0?users=user1@xxx.com; https://teams.microsoft.com/l/chat/0/0?users=user2@xxx.com; https://teams.microsoft.com/l/chat/0/0?users=user3@xxx.com

the out put should be as below e.g request id 318264, there are two users so I need to see Program Managers  = user1 and user 2 , and when I click on user1 --> it should open team link of user1  and when I click on user2 --> it should open team link of user2.

same way for request id 333089. It has 10 users so eash users should show up and I should be able to click on each user which should open team link of respective user. Hope I make sense. 

Please let me know if there is a better way to send a file as an attachment. Thank you.

[BA_Pete]

Hi @Patv ,

Paste the following code over the default code in a new blank query and you'll be able to see the steps I took to complete this:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("3ZTPCoJAEIdfRfYs7uw/3U2i3kM8iBgGieFu4OOnDkEGHWqCqMt+h51v2Bl+bFEwJbVyhsXs4ptBTGxDOPsN56GpOp90x3rofX8ISd13/MTrtgocOOzmcr9dpP04jvM1K+OpnwKZuVu/PJohEYrUPY9ediXBVeuphFXSfnBLwspUP2zpa+vBJ0kBBmgjLuddFBTYJ1FAaIRBpIgMYREOIeAnw/OGqwmuIbgpwc0IriW4juAKWMdfg7XwV/9WeQU=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [REQUEST_ID = _t, PROG_MANAGER = _t, PROG_MANAGERS_TEAMS_URL = _t]),
    removeSpaces = Table.ReplaceValue(Source," ","",Replacer.ReplaceText,{"PROG_MANAGER", "PROG_MANAGERS_TEAMS_URL"}),
    addProgMgrList = Table.AddColumn(removeSpaces, "progMgrList", each Text.Split([PROG_MANAGER], ";")),
    addProgMgrUrlList = Table.AddColumn(addProgMgrList, "progMgrUrlList", each Text.Split([PROG_MANAGERS_TEAMS_URL], ";")),
    addZippedLists = Table.AddColumn(addProgMgrUrlList, "listZip", each List.Zip({[progMgrList], [progMgrUrlList]})),
    expandZippedLists = Table.ExpandListColumn(addZippedLists, "listZip"),
    extractZippedValues = Table.TransformColumns(expandZippedLists, {"listZip", each Text.Combine(List.Transform(_, Text.From), "|"), type text}),
    splitExtractedValues = Table.SplitColumn(extractZippedValues, "listZip", Splitter.SplitTextByEachDelimiter({"|"}, QuoteStyle.Csv, false), {"progMgr", "progMgrUrl"}),
    remOthCols = Table.SelectColumns(splitExtractedValues,{"REQUEST_ID", "progMgr", "progMgrUrl"})
in
    remOthCols

SUMMARY:

1) Create lists from both [PROG_MGRS] and [PROG_MGR_URL]

2) Zip the lists together - this assumes that your users and their URLs are in the same order in their respective fields

3) Expand zipped lists to new rows

4) Extract values with any delimiter not found in either usernames or URLs

5) Split extracted values by same delimiter

Using the table in this structure, I can ouput to either single-row or multiple row format in a Matrix visual:

Pete

[Patv]

Thank you very very much Pete for replying and seems it's a solution I am looking for. But it seems I did not understand the steps correclty  (due to my lack of knowledge/terminology in Power BI world and also I am newbie). As per your summary steps.. 

SUMMARY:

1) Create lists from both [PROG_MGRS] and [PROG_MGR_URL] --> How do I create it?

2) Zip the lists together - this assumes that your users and their URLs are in the same order in their respective fields -->They are in respective field but how to zip the lists together?

3) Expand zipped lists to new rows --> How ?

4) Extract values with any delimiter not found in either usernames or URLs --> How?

5) Split extracted values by same delimiter

I am really sorry that I am asking so much of your time. I really appreciate your help which is boosting my confidence. Would you mind sending me pictures of steps? Thank you and again apologize for asking too much of your time.

This is how my sample table looklike in power bi.

Thank you!

[BA_Pete · Accepted solution]

Hi @Patv ,

No worries. Here’s the steps in answer to your questions:

1) You create these two lists as new columns. Create a new custom column, call it ‘progMgrList’, and put this in as the calculation:

Text.Split([PROG_MANAGER], ";")

Do the same again for a new column called ‘progMgrUrlList’ with this calculation:

Text.Split([PROG_MANAGERS_TEAMS_URL], ";")

2) Again, add another new custom column, call it ‘listZip’, and enter this as the calculation:

List.Zip({[progMgrList], [progMgrUrlList]})

3) At the right of the column header of your new [listZip] column, there is a button which looks like two arrows turning away from each other. Click this and choose ‘Expand to New Rows’ from the dropdown options.

4) Hit the same button again, but, this time, choose the ‘Extract Values’ option from the dropdown. When prompted to choose a delimiter, select ‘Custom’ from the dropdown and type “|“ (pipe character) into the input field. You don’t have to use the pipe character, I just used this as it’s very unlikely that it’s used in your usernames, and never used in URLs.

5) Select your [listZip] column that now contains usernames concatenated with their URLs. Go to Transform tab > Split Column > By Delimiter. Again, when choosing the delimiter to split by, go to Custom then enter the pipe character into the input box.

Pete

[Patv]

Hi Pete, 

Thank you very very much again for great step by step explanations. All steps worked. I am very close now. somehow I couldn't do two things.  please see attached screenshot.

1. How did you set up all URLs in Metrix Visualization? I was able to do that in Table visulization by changing  "data Category" to 'Web URL' ( attached screen). If I try to apply conditional formatting, it shows first, last  count(distincts), count. 

2. I am not able to order user1. user2, user3 in ascending order somehow. if you see in screen shot, it it in user2, user3, user1 order. I tried to change ascending/decending order but did not work.

Thank you!

[BA_Pete · Accepted solution]

Hi @Patv ,

1) In the matrix visual, to get the single-row view, set it up like this:

Rows = Request ID

Columns = listZip.1

Values = listZip.2

To get the multi-row view, do this:

Rows = listZip.1

Columns = Request ID

Values = listZip.2

The reason you get 'First', 'Last' etc. is that you are using a field that can theoretically have multiple values available within the visual context. This is normal. If you want to get around this you can create a measure that explicitly selects a single URL value, something like this:

_teamsURL = MAX(yourTable[listZip.2])

2) To get the sorting correct, select the Power Query step BEFORE the one where you make the progMgrList column. Multi-select (Ctrl+click) [PROG_MANAGER] and [PROG_MANAGERS_TEAMS_URL]. Go to the Transform tab > Replace Values. Put a space (" ") in the Value To Find box. Leave the Replace With box empty and hit ok. Apply your query to the model and you should get alphabetical orting.

Pete

[Patv]

 This solves my problem. Thank you Pete for all taking time and teaching me step by steps. I really appreciate it. You should start making Youtube videos on power bi. 🙂  I learn something new in Power bi as well. Again thank you a million. 

-Vpat