- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Roster by Location
HI There,
I am importing a simple table and wish to have the locations as the headers to - using power query:)
See attached file
Tab 1 is current Format
Sheet 2 is desired format,
Can you assist....:)
Solved! Go to Solution.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Related to your question, just copy and past the next code into advance editor.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("rZRLq8IwEIX/SuhaMJna51IXirhTcCEueiFooA9p4v9XaavxTptOwWUOnY8zZ6ZzOnliMRd8DhwWjIcp597MlkTcSMusNmyvipuuytfzrlUptWbCO8++GQlmRI20llV9kWyj8txGwH+E8AcRy+qPrZ5WZP18HKQ2nYHgY4C31eFbAj8NEmJ1F0GAIlhlyuSqZMerMrKnARKEYiHB1ZF7CtjBMMM9Bet7norgGwHQSNuskJrtC2WuPbsQ4hxClMMPECNhRJgRTbQRY0Q8DQECjcSSaGOlMXZKZyRG93f1+RhYToCPg7ZrSyJ2QWKMdEFiOBechhiYaYvw8Tz8qWmQGCNp4KMLPRfT7YPEGPGBzw5MPV00hsPH+QE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Start = _t, End = _t, Employee = _t, Location = _t]),
#"Grouped Rows" = Table.Group(Source, {"Location"}, {{"Count", each _, type table [Start=nullable text, End=nullable text, Employee=nullable text, Location=nullable text]}}),
#"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([Count],"Index",0)),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Location", "Count"}),
#"Expanded Custom" = Table.ExpandTableColumn(#"Removed Columns", "Custom", {"Start", "End", "Employee", "Location", "Index"}, {"Start", "End", "Employee", "Location", "Index"}),
#"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Expanded Custom", {"Location", "Index"}, "Attribute", "Value"),
#"Merged Columns" = Table.CombineColumns(#"Unpivoted Columns",{"Location", "Attribute"},Combiner.CombineTextByDelimiter(" | ", QuoteStyle.None),"Merged"),
#"Pivoted Column" = Table.Pivot(#"Merged Columns", List.Distinct(#"Merged Columns"[Merged]), "Merged", "Value"),
#"Removed Columns1" = Table.RemoveColumns(#"Pivoted Column",{"Index"})
in
#"Removed Columns1"
result in the next image
as it is impossible have two row headers in power query and also header columns should be unique (cant have three column with name Business 1) I combined your first two rows.
If this answer helped resolve your issue, please consider marking it as the accepted answer. And if you found my response helpful, I'd appreciate it if you could give me kudos.
Thank you!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi @Babycakes_00 ,
Pls has your problem been solved? If so, accept the reply as a solution. This will make it easier for the future people to find the answer quickly.
If not, please provide a more detailed description, preferably some virtual sample data, and the expected results.
Best Regards,
Stephen Tao
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Start | End | Employee | Location |
14/10/2024 06:00 | 14/10/2024 18:00 | Bart Simpson | Business 1 |
14/10/2024 09:00 | 14/10/2024 17:00 | George Hill | Business 2 |
14/10/2024 13:00 | 14/10/2024 17:00 | Bob Carter | Test 1 |
15/10/2024 00:00 | 16/10/2024 23:59 | Bob Carter | Test 1 |
15/10/2024 06:00 | 15/10/2024 18:00 | Caitlin White | Business 2 |
15/10/2024 06:00 | 15/10/2024 18:00 | Bob Carter | Test 1 |
15/10/2024 09:00 | 15/10/2024 17:00 | Bart Simpson | Business 2 |
15/10/2024 09:00 | 15/10/2024 17:00 | George Hill | Business 2 |
15/10/2024 10:15 | 15/10/2024 22:15 | James Smith | Business 1 |
16/10/2024 06:00 | 16/10/2024 18:00 | James Smith | Business 1 |
16/10/2024 06:00 | 16/10/2024 18:00 | Bart Simpson | Business 2 |
17/10/2024 06:00 | 17/10/2024 18:00 | James Smith | Business 1 |
18/10/2024 06:00 | 18/10/2024 18:00 | James Smith | Business 1 |
21/10/2024 09:00 | 21/10/2024 17:00 | Bart Simpson | Business 2 |
21/10/2024 09:00 | 21/10/2024 17:00 | Lisa Simpson | Business 2 |
21/10/2024 13:00 | 21/10/2024 17:00 | Bob Carter | Test 1 |
22/10/2024 08:00 | 22/10/2024 17:00 | Bart Simpson | Business 2 |
22/10/2024 08:00 | 22/10/2024 17:00 | Lisa Simpson | Business 2 |
22/10/2024 08:00 | 22/10/2024 17:00 | George Hill | Business 2 |
22/10/2024 08:00 | 22/10/2024 17:00 | James Smith | Business 2 |
23/10/2024 09:00 | 23/10/2024 17:00 | Bart Simpson | Business 2 |
23/10/2024 09:00 | 23/10/2024 17:00 | Lisa Simpson | Business 2 |
24/10/2024 09:00 | 24/10/2024 17:00 | Bart Simpson | Business 2 |
24/10/2024 09:00 | 24/10/2024 17:00 | Lisa Simpson | Business 2 |
25/10/2024 09:00 | 25/10/2024 17:00 | Bart Simpson | Business 2 |
25/10/2024 09:00 | 25/10/2024 17:00 | Lisa Simpson | Business 2 |
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Related to your question, just copy and past the next code into advance editor.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("rZRLq8IwEIX/SuhaMJna51IXirhTcCEueiFooA9p4v9XaavxTptOwWUOnY8zZ6ZzOnliMRd8DhwWjIcp597MlkTcSMusNmyvipuuytfzrlUptWbCO8++GQlmRI20llV9kWyj8txGwH+E8AcRy+qPrZ5WZP18HKQ2nYHgY4C31eFbAj8NEmJ1F0GAIlhlyuSqZMerMrKnARKEYiHB1ZF7CtjBMMM9Bet7norgGwHQSNuskJrtC2WuPbsQ4hxClMMPECNhRJgRTbQRY0Q8DQECjcSSaGOlMXZKZyRG93f1+RhYToCPg7ZrSyJ2QWKMdEFiOBechhiYaYvw8Tz8qWmQGCNp4KMLPRfT7YPEGPGBzw5MPV00hsPH+QE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Start = _t, End = _t, Employee = _t, Location = _t]),
#"Grouped Rows" = Table.Group(Source, {"Location"}, {{"Count", each _, type table [Start=nullable text, End=nullable text, Employee=nullable text, Location=nullable text]}}),
#"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([Count],"Index",0)),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Location", "Count"}),
#"Expanded Custom" = Table.ExpandTableColumn(#"Removed Columns", "Custom", {"Start", "End", "Employee", "Location", "Index"}, {"Start", "End", "Employee", "Location", "Index"}),
#"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Expanded Custom", {"Location", "Index"}, "Attribute", "Value"),
#"Merged Columns" = Table.CombineColumns(#"Unpivoted Columns",{"Location", "Attribute"},Combiner.CombineTextByDelimiter(" | ", QuoteStyle.None),"Merged"),
#"Pivoted Column" = Table.Pivot(#"Merged Columns", List.Distinct(#"Merged Columns"[Merged]), "Merged", "Value"),
#"Removed Columns1" = Table.RemoveColumns(#"Pivoted Column",{"Index"})
in
#"Removed Columns1"
result in the next image
as it is impossible have two row headers in power query and also header columns should be unique (cant have three column with name Business 1) I combined your first two rows.
If this answer helped resolve your issue, please consider marking it as the accepted answer. And if you found my response helpful, I'd appreciate it if you could give me kudos.
Thank you!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Business 1 | Business 1 | Business 1 | Business 2 | Business 2 | Business 2 | Test 1 | Test 1 | Test 1 |
Employee | Start | End | Employee | Start | End | Employee | Start | End |
Bart Simpson | 14/10/2024 06:00 | 14/10/2024 18:00 | George Hill | 14/10/2024 09:00 | 14/10/2024 17:00 | Bob Carter | 14/10/2024 13:00 | 14/10/2024 17:00 |
James Smith | 15/10/2024 10:15 | 15/10/2024 22:15 | Caitlin White | 15/10/2024 06:00 | 15/10/2024 18:00 | Bob Carter | 15/10/2024 00:00 | 16/10/2024 23:59 |
James Smith | 16/10/2024 06:00 | 16/10/2024 18:00 | Bart Simpson | 15/10/2024 09:00 | 15/10/2024 17:00 | Bob Carter | 15/10/2024 06:00 | 15/10/2024 18:00 |
James Smith | 17/10/2024 06:00 | 17/10/2024 18:00 | George Hill | 15/10/2024 09:00 | 15/10/2024 17:00 | Bob Carter | 21/10/2024 13:00 | 21/10/2024 17:00 |
James Smith | 18/10/2024 06:00 | 18/10/2024 18:00 | Bart Simpson | 16/10/2024 06:00 | 16/10/2024 18:00 | |||
Bart Simpson | 21/10/2024 09:00 | 21/10/2024 17:00 | ||||||
Lisa Simpson | 21/10/2024 09:00 | 21/10/2024 17:00 | ||||||
Bart Simpson | 22/10/2024 08:00 | 22/10/2024 17:00 | ||||||
Lisa Simpson | 22/10/2024 08:00 | 22/10/2024 17:00 | ||||||
George Hill | 22/10/2024 08:00 | 22/10/2024 17:00 | ||||||
James Smith | 22/10/2024 08:00 | 22/10/2024 17:00 | ||||||
Bart Simpson | 23/10/2024 09:00 | 23/10/2024 17:00 | ||||||
Lisa Simpson | 23/10/2024 09:00 | 23/10/2024 17:00 | ||||||
Bart Simpson | 24/10/2024 09:00 | 24/10/2024 17:00 | ||||||
Lisa Simpson | 24/10/2024 09:00 | 24/10/2024 17:00 | ||||||
Bart Simpson | 25/10/2024 09:00 | 25/10/2024 17:00 | ||||||
Lisa Simpson | 25/10/2024 09:00 | 25/10/2024 17:00 |
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Helpful resources
Join us at the Microsoft Fabric Community Conference
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
We want your feedback!
Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.
Microsoft Fabric Community Conference 2025
Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.