Need Help on a table

[Theeshk93]

Hi,

It will be much appreaciated if someone can help in the below query please.

Problem - I'm creating a table with the names of managers and the departments they are incharge of. My source table consists of Columns with Manager and Department. I need a visual which show the managers and their department. Some departments can fall under 2 or more managers. My challenge is removing the blank space (rows) in the table visual above the deparments. I have attached an example below. I want the blank space above John and Louis removed and the departments moved to start from the first row of the table. In my original table in Power Query, it is blank as well.  Thanks in advance.

Ben	John	Louis
IT
Finance
	Womens
	Procument
		Sales
		Finance

[v-sgandrathi]

Hi @Theeshk93,

Hi,

I wanted to check in your situation regarding the issue. Have you resolved it? If you have, please consider marking the reply as Accepted solution and give Kudos that helped you. It would be greatly appreciated by others in the community who may have the same question.

Thann you,
Sahasra.

[Theeshk93]

Hi Sahasra, 

My problem is not solved yet, I have responded above.TIA

[SundarRaj]

Hi @Theeshk93, here's another solution. Thanks

M Code used:

( The Table used here that is the Source is the above table mentioned above by you )

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WckrNU9JR8srPAFE++aWZxUqxOtFKniFArgIYg7humXmJecmpKGIgVnh+bmpeMYpQQFF+cilQtARFFISDE3NSi1FEYMbGxgIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}}),
List = List.Transform ( Table.ToColumns ( #"Changed Type" ) , each List.Select ( _ , each not ( _ = " " ) ) ),
Table = Table.PromoteHeaders ( Table.FromColumns ( List ) )
in
Table

[danextian · Accepted solution]

Hi @Theeshk93 

In the query editor, you can unpivot all columns and then remove blank rows from the resulting value column and then add an index or order column for each unique department per manager either with DAX or M. You can use this order column to create a kind of invisible key so provide a row context for each department per manager so all

Please refer to the attached pbix for the details.

[Nasif_Azam]

Hey @Theeshk93 ,

Thanks for sharing the screenshot and your problem context. You're trying to create a matrix-style table visual in Power BI (or similar), where each manager’s name is a column header and their departments are listed below but you're seeing blank rows above the departments, especially under John and Louis.

Solutions

Option 1: Create Separate Tables for Each Manager

To avoid blank rows:

1. Use measures or calculated tables to filter the departments per manager:

   Ben_Departments = 
   FILTER('DepartmentTable', 'DepartmentTable'[Manager] = "Ben")

2. Then create a table visual per manager or concatenate them using UNION and add a [Manager] column to make one clean table.

Option 2: Create a Custom Table Visual

If you want a side-by-side table like your image:

• Create this shape manually in Power BI using:

  FinalDisplayTable =
  DATATABLE(
    "Ben", STRING,
    "John", STRING,
    "Louis", STRING,
    {
      {"IT",      BLANK(), BLANK()},
      {"Finance", BLANK(), BLANK()},
      {BLANK(),  "Womens", BLANK()},
      {BLANK(),  "Procurement", BLANK()},
      {BLANK(),  BLANK(), "Sales"},
      {BLANK(),  BLANK(), "Finance"}
    }
  )

You can build this DATATABLE using Power Query transformations as well, such as pivoting and filling blank rows upward.

For Detailed Information:

Microsoft Docs – Matrix Visual

Power Query Fill Down Docs

Radacad Tutorial

If you found this solution helpful, please consider accepting it and giving it a kudos (Like) it’s greatly appreciated and helps others find the solution more easily.

Best Regards,
Nasif Azam