Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Issues creating visuals due to the sharepoint data

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!

 

Need Help

2 Replies

  • v-kkf-msft's avatar
    v-kkf-msft
    Community Support
    4 years ago

    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.