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

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

Reply
Babycakes_00
New Member

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
New Member

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

 

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors