Reply
Babycakes_00
Regular Visitor

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....:)

 

1 ACCEPTED 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

 

Omid_Motamedise_0-1729032785065.png

 

 

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!

 

View solution in original post

5 REPLIES 5
v-stephen-msft
Community Support
Community Support

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

 

Babycakes_00
Regular Visitor

StartEndEmployeeLocation
14/10/2024 06:0014/10/2024 18:00Bart SimpsonBusiness 1
14/10/2024 09:0014/10/2024 17:00George HillBusiness 2
14/10/2024 13:0014/10/2024 17:00Bob CarterTest 1
15/10/2024 00:0016/10/2024 23:59Bob CarterTest 1
15/10/2024 06:0015/10/2024 18:00Caitlin WhiteBusiness 2
15/10/2024 06:0015/10/2024 18:00Bob CarterTest 1
15/10/2024 09:0015/10/2024 17:00Bart SimpsonBusiness 2
15/10/2024 09:0015/10/2024 17:00George HillBusiness 2
15/10/2024 10:1515/10/2024 22:15James SmithBusiness 1
16/10/2024 06:0016/10/2024 18:00James SmithBusiness 1
16/10/2024 06:0016/10/2024 18:00Bart SimpsonBusiness 2
17/10/2024 06:0017/10/2024 18:00James SmithBusiness 1
18/10/2024 06:0018/10/2024 18:00James SmithBusiness 1
21/10/2024 09:0021/10/2024 17:00Bart SimpsonBusiness 2
21/10/2024 09:0021/10/2024 17:00Lisa SimpsonBusiness 2
21/10/2024 13:0021/10/2024 17:00Bob CarterTest 1
22/10/2024 08:0022/10/2024 17:00Bart SimpsonBusiness 2
22/10/2024 08:0022/10/2024 17:00Lisa SimpsonBusiness 2
22/10/2024 08:0022/10/2024 17:00George HillBusiness 2
22/10/2024 08:0022/10/2024 17:00James SmithBusiness 2
23/10/2024 09:0023/10/2024 17:00Bart SimpsonBusiness 2
23/10/2024 09:0023/10/2024 17:00Lisa SimpsonBusiness 2
24/10/2024 09:0024/10/2024 17:00Bart SimpsonBusiness 2
24/10/2024 09:0024/10/2024 17:00Lisa SimpsonBusiness 2
25/10/2024 09:0025/10/2024 17:00Bart SimpsonBusiness 2
25/10/2024 09:0025/10/2024 17:00Lisa SimpsonBusiness 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

 

Omid_Motamedise_0-1729032785065.png

 

 

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 1Business 1Business 1Business 2Business 2Business 2Test 1Test 1Test 1
EmployeeStartEndEmployeeStartEndEmployeeStartEnd
Bart Simpson14/10/2024 06:0014/10/2024 18:00George Hill14/10/2024 09:0014/10/2024 17:00Bob Carter14/10/2024 13:0014/10/2024 17:00
James Smith15/10/2024 10:1515/10/2024 22:15Caitlin White15/10/2024 06:0015/10/2024 18:00Bob Carter15/10/2024 00:0016/10/2024 23:59
James Smith16/10/2024 06:0016/10/2024 18:00Bart Simpson15/10/2024 09:0015/10/2024 17:00Bob Carter15/10/2024 06:0015/10/2024 18:00
James Smith17/10/2024 06:0017/10/2024 18:00George Hill15/10/2024 09:0015/10/2024 17:00Bob Carter21/10/2024 13:0021/10/2024 17:00
James Smith18/10/2024 06:0018/10/2024 18:00Bart Simpson16/10/2024 06:0016/10/2024 18:00   
   Bart Simpson21/10/2024 09:0021/10/2024 17:00   
   Lisa Simpson21/10/2024 09:0021/10/2024 17:00   
   Bart Simpson22/10/2024 08:0022/10/2024 17:00   
   Lisa Simpson22/10/2024 08:0022/10/2024 17:00   
   George Hill22/10/2024 08:0022/10/2024 17:00   
   James Smith22/10/2024 08:0022/10/2024 17:00   
   Bart Simpson23/10/2024 09:0023/10/2024 17:00   
   Lisa Simpson23/10/2024 09:0023/10/2024 17:00   
   Bart Simpson24/10/2024 09:0024/10/2024 17:00   
   Lisa Simpson24/10/2024 09:0024/10/2024 17:00   
   Bart Simpson25/10/2024 09:0025/10/2024 17:00   
   Lisa Simpson25/10/2024 09:0025/10/2024 17:00   

wdx223_Daniel_0-1729042601901.png

 

avatar user

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

Dec Fabric Community Survey

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.

December 2024

A Year in Review - December 2024

Find out what content was popular in the Fabric community during 2024.

Top Solution Authors (Last Month)
Top Kudoed Authors (Last Month)