Move columns into one column

[dd88]

in Table view, I have a table with columns of different tasks types, tasks due date and name assigned to task. the data source is an excel spreadsheet. eg of layout. 

Name	Task 1	Task 1 dueDate	Task 2	Task 2 DueDate	Task 3	Task 3 DueDate
A	Task 1	17/05/2024	Task 2	21/07/2024	Task 3	10/10/2024
B	Task 1	24/07/2024	Task 2	20/08/2024	Task 3	11/11/2024
C	Task 1	306/2024	Task 2	4/09/2024	Task 3	15/09/2024

For report requirements, the users would like to filter on tasks types, and the Next DueDate for a task.  which means the design is to move all the columns for different tasks into one column. (can create a new table) for example the layout

Task Type	Name	Task due date
Task 1	A	17/05/2024
Task 1	B	24/07/2024
Task 1	C	306/2024
Task 2	A	21/07/2024
Task 2	B	20/08/2024
Task 2	C	4/09/2024
Task 3	A	10/10/2024
Task 3	B	11/11/2024
Task 3	C	15/09/2024

Ideally to create a new table in TableView. How can I do this in powerbi.

TIA

[dd88]

@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

 

[dd88]

@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

 

[Ashish_Mathur]

Hi,

The M code i shared with you earlier should work fine.

[dd88]

@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

[dd88]

@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]

Hi,

I am unable to understand your data layout.

[Ashish_Mathur]

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.

[Ashish_Mathur]

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.

[dd88]

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 

[VahidDM]

You can apply all changes on your table in power query.

download my sample file and follow the steps

regards

vahid

[manvishah17]

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!

[VahidDM]

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 

[dd88]

Thank you @VahidDM  the sample file is great ..

[Fthrs_Analytics]

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