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!! 

LinkedIn | Twitter | Blog | YouTube 

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 

In Power Query, you can select the Name column and then unpivot other columns.

HI dd88 ,
I created sample Pbix file. 

  The steps are :

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,

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.

VahidDM Ashish_Mathur 

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 

Ashish_Mathur VahidDM 

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

Ashish_Mathur  hoping this is clearer ..

orginal data source layout in Excel.

Total of 13 different tasks columns/fields

the requirements for the powerbi report, all tasks to be in 1 column to allow filter etc

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

Ashish_Mathur  hoping this is clearer ..

orginal data source layout in Excel.

Total of 13 different tasks columns/fields

the requirements for the powerbi report, all tasks to be in 1 column to allow filter etc

how can this be achieved?

I thought Advanced Power Query - Unpivot Columns with Multiple Headers, would solve the problem?

tia