@Ashish_Mathur hoping this is clearer ..
orginal data source layout in Excel.
Total of 13 different tasks columns/fields
| Name | Task 1 | Task 1 dueDate | Task 2 | Task 2 DueDate | Task 3 | Task 3 DueDate | Task 13 | Task 13 DueDate | Department | Team | Category 1 | Category 2 | comments |
| A | Task 1 | 17/05/2024 | Task 2 | 21/07/2024 | Task 3 | N/A | Task 13 | 20/08/2024 | HR | Recruitment | |||
| B | Task 1 | 24/07/2024 | Task 2 | N/A | Task 3 | N/A | Task 13 | N/A | Finance | Accounts | |||
| C | Task 1 | 3/06/2024 | Task 2 | 4/09/2024 | Task 3 | 15/09/2024 | Task 13 | 11/11/2024 | Marketing | Socials team | working on project B. date to complete Aug 2024 |
the requirements for the powerbi report, all tasks to be in 1 column to allow filter etc
| Staff name | All Tasks | TaskDueDate | Department | Team | Category 1 | Category 2 | comments |
| A | Task 1 | 17/05/2024 | HR | Recruitment | |||
| A | Task 2 | 21/07/2024 | HR | Recruitment | |||
| A | Task 3 | N/A | HR | Recruitment | |||
| A | Task 13 | 20/08/2024 | HR | Recruitment | |||
| B | Task 1 | 24/07/2024 | Finance | Accounts | |||
| B | Task 2 | N/A | Finance | Accounts | |||
| B | Task 3 | N/A | Finance | Accounts | |||
| B | Task 13 | N/A | Finance | Accounts | |||
| C | Task 1 | 3/06/2024 | Marketing | Socials team | working on project B. date to complete Aug 2024 | ||
| C | Task 2 | 4/09/2024 | Marketing | Socials team | working on project B. date to complete Aug 2024 | ||
| C | Task 3 | 15/09/2024 | Marketing | Socials team | working on project B. date to complete Aug 2024 | ||
| C | Task 13 | 11/11/2024 | Marketing | Socials team | working on project B. date to complete Aug 2024 |
how can this be achieved?
I thought Advanced Power Query - Unpivot Columns with Multiple Headers, would solve the problem?
tia
@Ashish_Mathur hoping this is clearer ..
orginal data source layout in Excel.
Total of 13 different tasks columns/fields
| Name | Task 1 | Task 1 dueDate | Task 2 | Task 2 DueDate | Task 3 | Task 3 DueDate | Task 13 | Task 13 DueDate | Department | Team | Category 1 | Category 2 | comments |
| A | Task 1 | 17/05/2024 | Task 2 | 21/07/2024 | Task 3 | N/A | Task 13 | 20/08/2024 | HR | Recruitment | |||
| B | Task 1 | 24/07/2024 | Task 2 | N/A | Task 3 | N/A | Task 13 | N/A | Finance | Accounts | |||
| C | Task 1 | 3/06/2024 | Task 2 | 4/09/2024 | Task 3 | 15/09/2024 | Task 13 | 11/11/2024 | Marketing | Socials team | working on project B. date to complete Aug 2024 |
the requirements for the powerbi report, all tasks to be in 1 column to allow filter etc
| Staff name | All Tasks | TaskDueDate | Department | Team | Category 1 | Category 2 | comments |
| A | Task 1 | 17/05/2024 | HR | Recruitment | |||
| A | Task 2 | 21/07/2024 | HR | Recruitment | |||
| A | Task 3 | N/A | HR | Recruitment | |||
| A | Task 13 | 20/08/2024 | HR | Recruitment | |||
| B | Task 1 | 24/07/2024 | Finance | Accounts | |||
| B | Task 2 | N/A | Finance | Accounts | |||
| B | Task 3 | N/A | Finance | Accounts | |||
| B | Task 13 | N/A | Finance | Accounts | |||
| C | Task 1 | 3/06/2024 | Marketing | Socials team | working on project B. date to complete Aug 2024 | ||
| C | Task 2 | 4/09/2024 | Marketing | Socials team | working on project B. date to complete Aug 2024 | ||
| C | Task 3 | 15/09/2024 | Marketing | Socials team | working on project B. date to complete Aug 2024 | ||
| C | Task 13 | 11/11/2024 | Marketing | Socials team | working on project B. date to complete Aug 2024 |
also attached as screenshots as not all data fitted into the table in this post
orginal data source layout
power bi report requirement
how can this be achieved?
I thought Advanced Power Query - Unpivot Columns with Multiple Headers, would solve the problem?
tia
The example provided was a simple table with 3 columns/fields.
The real data has 13 different tasks/colmuns.
The data in task columns contains either the data, or NA. formatted as text field.
+ additional fields to display alongside the one column are
worker, All tasks, due date, department, team, category 1, category 2, and comments
There is a total of 116 records.
If I manually merge all tasks in 1 column, total records are 1508.
I select all 13 task columns, Unpivot selected columns.
The results
mnay rows are created however mostly blank rows.
Only 2 records display. I checked the orginial data source. These 2 records are the only records with date in ALL the task columns.
The other blank rows, the records in tasks columns contain either a date or N/A
How can I achieve this?
Update --- I found the following which may solve the problem ..
Unpivot Cross Tabulated Data with Multiple Headers and Columns
https://www.youtube.com/watch?v=O2d5Ec10E1E
Advanced Power Query - Unpivot Columns with Multiple Headers
https://www.youtube.com/watch?v=y39LFLLbvk0
TIA
The example used was a simple table of 3 fields. to do a test run and it worked.
The real data contains the following ..
13 different tasks/fields. The columns formatted as text.
and contain values either the date or N/A.
The 13 different tasks to be in the one column.
and an additional 6 fields needed to align and be displayed next to the tasks eg
Staff name, All Tasks, date due, department, team, cateory 1, category 2, comments
Total fields 8
Total records 116
I select all 13 fields the different tasks, and Unpivot Select columns.
The results
1,433 rows created
however only 2 data records display.
From what I can see the 2 records contain only date values in all 13 tasks.
All other records are blank rows 1 column. The records orginal columns contain a mix of data in tasks types, ie date or N/A
Is there a way to bring ALL data for the 116 records into the 1 column, which will give a total of 1508 records?
TIA
Hi,
This M code works
let
Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Name"}, "Task type", "Date"),
#"Added Custom" = Table.AddColumn(#"Unpivoted Other Columns", "Custom", each [#"Task type"]=[Date]),
#"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([Custom] = false)),
#"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Custom"}),
#"Replaced Value" = Table.ReplaceValue(#"Removed Columns","dueDate","",Replacer.ReplaceText,{"Task type"}),
#"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","DueDate","",Replacer.ReplaceText,{"Task type"}),
#"Changed Type" = Table.TransformColumnTypes(#"Replaced Value1",{{"Date", type date}})
in
#"Changed Type"Hope this helps.
Hi,
This M code works
let
Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Name"}, "Task type", "Date"),
#"Added Custom" = Table.AddColumn(#"Unpivoted Other Columns", "Custom", each [#"Task type"]=[Date]),
#"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([Custom] = false)),
#"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Custom"}),
#"Replaced Value" = Table.ReplaceValue(#"Removed Columns","dueDate","",Replacer.ReplaceText,{"Task type"}),
#"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","DueDate","",Replacer.ReplaceText,{"Task type"}),
#"Changed Type" = Table.TransformColumnTypes(#"Replaced Value1",{{"Date", type date}})
in
#"Changed Type"Hope this helps.
thank you all, thats great.
Let me know if I am on the right track ..
The orginial table to remain untouched.
Therefore, in Edit query, on the orginal table I selected Reference. This creates a reference table. I deleted columns no longer needed. I noticed calculated columns created in the orginial table, are not available in the Reference table. Does that mean in Table view, I need to add the calculated columns. Once completed. in the Reference table I will unpivot the columns
You can apply all changes on your table in power query.
download my sample file and follow the steps
regards
vahid
HI @dd88 ,
I created sample Pbix file.
let
Source = Excel.Workbook(File.Contents("C:\Users\**bleep**al Clinic(Rutvi)\Downloads\Book1.xlsx"), null, true),
Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
#"Changed Type" = Table.TransformColumnTypes(Sheet1_Sheet,{{"Column1", type text}, {"Column2", type text}, {"Column3", type any}, {"Column4", type text}, {"Column5", type any}, {"Column6", type text}, {"Column7", type any}}),
#"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]),
#"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Column1", type text}, {"Column2", type text}, {"Column3", type any}, {"Column4", type text}, {"Column5", type any}, {"Column6", type text}, {"Column7", type any}}),
#"Promoted Headers1" = Table.PromoteHeaders(#"Changed Type1", [PromoteAllScalars=true]),
#"Changed Type2" = Table.TransformColumnTypes(#"Promoted Headers1",{{"Name ", type text}, {"Task 1 ", type text}, {"Task 1 dueDate ", type any}, {"Task 2 ", type text}, {"Task 2 DueDate ", type date}, {" Task 3 ", type text}, {"Task 3 DueDate ", type date}}),
#"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type2", {"Name "}, "Attribute", "Value"),
#"Filtered Rows" = Table.SelectRows(#"Unpivoted Columns", each ([Attribute] = "Task 1 dueDate " or [Attribute] = "Task 2 DueDate " or [Attribute] = "Task 3 DueDate ")),
#"Split Column by Position" = Table.SplitColumn(#"Filtered Rows", "Attribute", Splitter.SplitTextByRepeatedLengths(7), {"Attribute.1", "Attribute.2", "Attribute.3"}),
#"Renamed Columns" = Table.RenameColumns(#"Split Column by Position",{{"Attribute.1", "Task"}, {"Value", "Due Date"}}),
#"Removed Columns" = Table.RemoveColumns(#"Renamed Columns",{"Attribute.2", "Attribute.3"})
in
#"Removed Columns"
If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!
Hi @dd88
You can use this in Power Query, just change the Source in the code:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUQpJLM5WMAQyDM31DUz1jQyMTGCiRkCGkaG+gTmKqDFIrYE+EIFFY3WilZyQzTEyQdcBNsdA38AC0xxDfSCCm+OMbI4xUIcZhjlAwy0xjTGFi8bGAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, #"Task 1" = _t, #"Task 1 DueDate " = _t, #"Task 2" = _t, #"Task 2 DueDate " = _t, #"Task 3" = _t, #"Task 3 DueDate " = _t]),
#"Unpivoted Columns" = Table.UnpivotOtherColumns(Source, {"Name"}, "Task Type", "Task due date"),
#"Filtered Rows" = Table.SelectRows(#"Unpivoted Columns", each Text.Contains([Task Type], "DueD")),
#"Replaced Value" = Table.ReplaceValue(#"Filtered Rows"," DueDate ","",Replacer.ReplaceText,{"Task Type"}),
#"Sorted Rows" = Table.Sort(#"Replaced Value",{{"Task Type", Order.Ascending}})
in
#"Sorted Rows"
Sample File attached.
If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
In Power Query, you can select the Name column and then unpivot other columns.
