Forum Discussion
How to make URL functionality work if values are comma/colon separated values.
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.
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
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
10 Replies
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
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.
