Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
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.
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!
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
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 |
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!
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 |
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
65 | |
61 | |
21 | |
18 | |
12 |