Matrix shows columns in ascending but not the order as per the power query

[nileshwh]

Hi 

I want to create the report based on below Multiple SharePoint List data.

SharePoint Lists
Dept ID	Dept Name
1	IT
2	HR
3	Finance
EmployeeDetails:
Employee ID	Employee Name	Dept ID
100	John	1
200	Linda	2
101	Steve	1
201	Ramesh	2
102	Raj	1

The report should be displayed in below format.

Dept ID	Dept Name	Employee ID	Employee Name	Employee ID	Employee Name	Employee ID	Employee Name
1	IT	100	John	101	Steve	102	Raj
2	HR	200	Linda	201	Ramesh

I have written the below Power Query using Power BI Desktop:

let
Source = Table.NestedJoin(DeptDetails, {"ID"}, EmpDetails, {"DeptID.lookupId"}, "EmpDetails", JoinKind.LeftOuter),
#"Expanded EmpDetails" = Table.ExpandTableColumn(Source, "EmpDetails", {"EmpID", "EmployeeName"}, {"EmpDetails.EmpID", "EmpDetails.EmployeeName"}),
#"Grouped Rows" = Table.Group(#"Expanded EmpDetails", {"DeptID"}, {{"AllRows", each Table.AddIndexColumn(_, "Index", 0, 1), type table}}),
#"Expanded AllRows" = Table.ExpandTableColumn(#"Grouped Rows", "AllRows", {"DeptName", "EmpDetails.EmpID", "EmpDetails.EmployeeName", "Index"}, {"AllRows.DeptName", "AllRows.EmpDetails.EmpID", "AllRows.EmpDetails.EmployeeName", "AllRows.Index"}),
IndexToText = Table.TransformColumns(#"Expanded AllRows",{{"AllRows.Index", each if _ = 0 then "" else Text.From(_), type text}}),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(IndexToText, {"DeptID", "AllRows.Index"}, "Attribute", "Value"),
#"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Unpivoted Other Columns", {{"AllRows.Index", type text}}, "en-US"),{"Attribute", "AllRows.Index"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Merged")
in
#"Merged Columns"

The output of this query is as below:

The merge column is having column names in sequence  i.e 1. DeptName, EmpID and then EmpName.

I used the Matrix visual in visualization and assign DeptID as Rows, Merged as Columns and Value as Value in Matrix

But Matrix shows the column in ascending order like All  deptName, then all EmpID and then All EmpName.

But I need to display the data as DeptName, EmpID, EmpName, DeptName1, EmpID1, EmployeeName1, DeptName2, EmpID2, EmployeeName2.

Please let me know where I can change column sequence

Regards,

Nilesh

[Greg_Deckler]

@nileshwh Sort by the first thing you want then hold down the Shift key to select the other columns you want to sort by in sequence.

[nileshwh]

@Greg_Deckler ,

Thank you for your quick reply.

But I am not cleared about your solution. Can you elaborate it in detail?  Sorting in Matrix at column level? I could not found any option for columns. If you provide me any sample solution then it will help me a lot in undestanding.

[Greg_Deckler]

@nileshwh I misunderstood and was thinking about a table. So where is AllRows.DeptName1, AllRows.DeptName2, AllRowsDeptName3 coming from?

[nileshwh]

@Greg_Deckler,

It is coming from Power query. Please check my first post which includes power query and its output as screenshot.

[Greg_Deckler]

@nileshwh Are you married to that PQ or would something like this work?

[nileshwh]

@Greg_Deckler 

I am fine with the output which is shown in screenshot. I will be more than happy with such outout. My actual requirement is what you have shown in screenshot. 

[Greg_Deckler]

@nileshwh OK, see attached. Forget the PQ stuff and just leave them as two separate tables. See Table30 and Table30a in attached PBIX below sig. Page 6.

[nileshwh]

@Greg_Deckler ,

I really apologize for my delayed response and thank you for providing the solution. It is working as per our discussion.  

While implementing this solution, I am facing some formating issues. It is showing DeptName below DeptID. Both column should be displayed in same position. Please find the screenshot below with highlighted in yellow. I checked your file where it shows in proper format but here I am not able to arrange deptid and deptname in same line horizontally

Secondly there is one modification in my current requirement. Please find the details below:

The data is stored in Multiple SharePoint list as per the below screenshot. Here two new list has been added

1. EmpProject is child list of EmpDetails list. EmpProject stores all the project details related to each employee.

2. LocationDetails - This list store the information about the locations where each department is located. 

Now I want to generate the report which will show data in single row for each dept.

I have provided the link below to view my pbix file and also excel file which contain report format and data 

Pbix and Excel with Report Format and data 

Please help me to generate the report in required format.

I really appriciate your help.

[Greg_Deckler]

@nileshwh For you first question, you need to go to the Format sub-pane of the Visualizations pane. Expand Row headers section and turn off Stepped layout. I'll have to look at the second request. I was not able to download the PBIX basically said "not found".

[nileshwh]

@Greg_Deckler , First issue is resolved(Formating the deptName Column).

Attachment option is not enable for my id in this forum so could not attached the files here. Is there any other way I can attach the files?

I am reattaching file links below:

Report file 

Pbix File 

[v-shex-msft]

Hi @nileshwh,

It sounds like you want to achieve custom sorting orders on your table records.
For your scenario, I'd like to suggest you take a look at the following blog about create a custom sorting table and setting the 'sort by column' property to achieve your requirement.

Custom Sorting in Power BI 

Regards,

Xiaoxin Sheng

[nileshwh]

Hi @v-shex-msft ,

Thank you for your response. I tried the options you mentioned in your post. But could not achieve the desired output.

I have one query created using three tables - DeptDetails, EmpDetails and EmpProjects

1. Tables:

One More Table: MatchColumns - This table is used to joing with query to display the record in desired sequence.

2. Query:  Merge-Dept-Emp-Projects

3. Visual : I added Matrix on the visualization page and assign rows, header, values as below:

But now Issue is it shows only 5 columns from the data.

Please refere  the Pbix file by using following linik

Index fo column Order 

Please help me to resolve this issue

[v-shex-msft]

HI @nileshwh,

You can extract the 'Merge' field values to a new table, add a custom column to check merge field values to setting priority number(deptName: 1, EmpID: 2, EmpName: 3), and config custom column sort by 'ASC'.
Then you can add an index column to the new table and setting the index as 'sort by column' property at data view side and build the relationship from this table to raw table.
After these steps, you can use new table merge field on your matrix and it will show as you wanted.

Regards,

Xiaoxin Sheng

[nileshwh]

Hi @v-shex-msft ,

Thank you for your reply.

I am not able to understand the solution you provided. Can you elaborate in details using my example provided in previous post? 

Please provide the details on below points

1. Merge field values in new table(how to create it and using which columns)

2. Custom column to setting priority numbers( How to create priority numbers in custom column)

3. setting the index as 'sort by column' property at data view side

4. build the relationship from this table to raw table

Really appreciate your help.

[nileshwh]

Hi @v-shex-msft ,

Waiting for your reply.

Regards,

[v-shex-msft]

HI @nileshwh,

I check your sample file but I can't do any custom on your pbix query edit side, these source data is invalid to view due to security reasons.

I create some dummy data as source data and build a sample pbix below, you can try it if helps:

Regards,

Xiaoxin Sheng