Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
dd88
Resolver I
Resolver I

Move columns into one column

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   
ATask 117/05/2024Task 221/07/2024 Task 310/10/2024
BTask 124/07/2024Task 220/08/2024 Task 311/11/2024
CTask 1306/2024Task 24/09/2024 Task 315/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 1A17/05/2024
Task 1B24/07/2024
Task 1C306/2024
Task 2A21/07/2024
Task 2B20/08/2024
Task 2C4/09/2024
Task 3A10/10/2024
Task 3B11/11/2024
Task 3C15/09/2024

 

 

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

 

TIA

14 REPLIES 14
dd88
Resolver I
Resolver I

@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 13Task 13 DueDate   Department  Team  Category 1  Category 2  comments
ATask 117/05/2024Task 221/07/2024 Task 3     N/ATask 1320/08/2024HRRecruitment   
BTask 124/07/2024Task 2N/ATask 3     N/ATask 13N/AFinanceAccounts   
CTask 13/06/2024Task 24/09/2024 Task 3     15/09/2024Task 1311/11/2024MarketingSocials 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 nameAll TasksTaskDueDateDepartmentTeamCategory 1Category 2comments
ATask 117/05/2024HRRecruitment   
ATask 221/07/2024HRRecruitment   
ATask 3N/AHRRecruitment   
ATask 1320/08/2024HRRecruitment   
BTask 124/07/2024FinanceAccounts   
BTask 2N/AFinanceAccounts   
BTask 3N/AFinanceAccounts   
BTask 13N/AFinanceAccounts   
CTask 13/06/2024MarketingSocials team  working on project B. date to complete Aug 2024
CTask 24/09/2024MarketingSocials team  working on project B. date to complete Aug 2024
CTask 315/09/2024MarketingSocials team  working on project B. date to complete Aug 2024
CTask 1311/11/2024MarketingSocials 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
Resolver I
Resolver I

@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 13Task 13 DueDate   Department  Team  Category 1  Category 2  comments
ATask 117/05/2024Task 221/07/2024 Task 3     N/ATask 1320/08/2024HRRecruitment   
BTask 124/07/2024Task 2N/ATask 3     N/ATask 13N/AFinanceAccounts   
CTask 13/06/2024Task 24/09/2024 Task 3     15/09/2024Task 1311/11/2024MarketingSocials 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 nameAll TasksTaskDueDateDepartmentTeamCategory 1Category 2comments
ATask 117/05/2024HRRecruitment   
ATask 221/07/2024HRRecruitment   
ATask 3N/AHRRecruitment   
ATask 1320/08/2024HRRecruitment   
BTask 124/07/2024FinanceAccounts   
BTask 2N/AFinanceAccounts   
BTask 3N/AFinanceAccounts   
BTask 13N/AFinanceAccounts   
CTask 13/06/2024MarketingSocials team  working on project B. date to complete Aug 2024
CTask 24/09/2024MarketingSocials team  working on project B. date to complete Aug 2024
CTask 315/09/2024MarketingSocials team  working on project B. date to complete Aug 2024
CTask 1311/11/2024MarketingSocials 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

 

Orginial data source layout in Excel.png

 

power bi report requirement

Report requirement.png

 

how can this be achieved?

 

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

tia

 

Hi,

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
dd88
Resolver I
Resolver I

@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
Resolver I
Resolver I

@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 

Hi,

I am unable to understand your data layout.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Ashish_Mathur
Super User
Super User

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_0-1716086919886.png

 


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Ashish_Mathur
Super User
Super User

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_0-1716086919886.png

 


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
dd88
Resolver I
Resolver I

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

manvishah17
Resolver V
Resolver V

HI @dd88 ,
I created sample Pbix file. Screenshot 2024-05-17 110902.png

 

Screenshot 2024-05-17 111217.png  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"

 

 

Screenshot 2024-05-17 111226.png

 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
Super User
Super User

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 @VahidDM  the sample file is great ..

Fthrs_Analytics
Frequent Visitor

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

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors