Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

New Member

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!




Community Support
Community Support

Hi @aserata ,


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.


    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}})
    #"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,

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

Super User
Super User

@aserata ,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:

Helpful resources

PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Community News

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors