Issues creating visuals due to the sharepoint data

[Anonymous]

Hi everyone,

I am working on creating a dashboard for my company, this dashboard is meant to analyze department capacity.  (this data is coming from a Sharepoint Intake form) So when a department has a request, they use Sharepoint to fill out the request. I currently have a few issues with the data:

1. When selecting departments on Sharepoint, each department has a few sub-departments. EX: If someone is choosing Healthcare, it may include three or four different groups within healthcare.

For my purposes, I want to group all of the healthcare together, I have tried indexes but I have had no luck.

2. As you can see here, the units are spaced by a comma. I was trying to create a group, but when I did, it thought each item was a list from the data. EX: If I wanted to split by group, it wanted to include every department on a data line.

If anyone out there has some suggestions, I would really appreciate it!

Thanks!

[v-kkf-msft]

Hi @Anonymous ,

Not sure if I understand correctly. If you want to show sub-departments in a cell with line breaks, you can try the following code.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUfLNTEnJSVXwTSzKTi3RCc5NzMlRcM7PzU0tSs5MzNHxSE3MKclITixKVdBVcMzJUYrViVZyAupzzgcKOfsARdH1gJU4A5X4l2SkFoF5LkAewiQdn8zEpMyczJJKsKQrYVe4xpQaGBiZBWNxTiwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Department = _t, #"Business Unit" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Department", type text}, {"Business Unit", type text}}),
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Changed Type", {{"Business Unit", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Business Unit"),
    #"Grouped Rows" = Table.Group(#"Split Column by Delimiter", {"Department"}, {{"Business Unit", each Text.Combine([Business Unit],"#(lf)"), type nullable text}})
in
    #"Grouped Rows"

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

Best Regards,
Winniz

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@Anonymous ,Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

The only thing I can think of as of now, is split by delimiter into rows

Split Column Power Query: https://youtu.be/FyO9Vmhcfag

https://www.tutorialgateway.org/how-to-split-columns-in-power-bi/