How to deal with multiple headers in data in Power BI

[Jacb]

Hi, 
I am trying to read in the data from PDF document that contains operational stats. 
The data from the PDF file looks like this.  

When the PDF file is read into Power BI - Power BI identifies that the data contains 4 headers Login etc, Agent ID, Agent Name and Date as shown below:

Column1	Column2	Column3	Column4	Column5	Column6	Column7	Column8	Column9	Column10	Column11	Column12
Log In	Log Out	Login Duration	Available T	Released T	Available In Call T	Released In Call T	In Call T	Media Type	Calls Handled	Avg T To Answer	Max T To Answer
Agent Id:	1111	null	null	null	null	null	null	null	null	null	null
Agent Name:	Joe Blogs	null	null	null	null	null	null	null	null	null	null
06/01/2025	null	null	null	null	null	null	null	null	null	null	null
09:55:51	14:36:57	04:41:06	03:35:16	01:05:50	00:00:00	00:00:00	00:00:00	(All)	0	00:00:00	00:00:31
14:45:56	18:13:05	03:27:09	03:22:06	00:05:03	00:00:00	00:00:00	00:00:00	(All)	0	00:00:00	00:00:15
null	Total:	08:08:15	06:57:22	01:10:53	00:00:00	00:00:00	00:00:00	null	0	00:00:00	00:00:31
Agent Id:	9999	null	null	null	null	null	null	null	null	null	null
Agent Name:	Millie Smith	null	null	null	null	null	null	null	null	null	null
06/01/2025	null	null	null	null	null	null	null	null	null	null	null
10:02:31	12:53:35	02:51:04	00:00:00	02:51:04	00:00:00	00:00:00	00:00:00	(All)	0	00:00:00	00:00:00
13:21:18	14:25:33	01:04:15	00:00:00	01:04:15	00:00:00	00:00:00	00:00:00	(All)	0	00:00:00	00:00:00
16:04:22	16:39:01	00:34:39	00:00:00	00:34:39	00:00:00	00:00:00	00:00:00	(All)	0	00:00:00	00:00:00
null	Total:	04:29:58	00:00:00	04:29:58	00:00:00	00:00:00	00:00:00	null	0	00:00:00	00:00:00
Agent Id:	1122	null	null	null	null	null	null	null	null	null	null
Agent Name:	Joe Black	null	null	null	null	null	null	null	null	null	null
06/01/2025	null	null	null	null	null	null	null	null	null	null	null
09:33:02	11:09:31	01:36:29	00:45:59	00:50:30	00:00:00	00:00:00	00:00:00	(All)	0	00:00:00	00:00:16
12:23:38	12:56:05	00:32:27	00:32:26	00:00:01	00:00:00	00:00:00	00:00:00	(All)	0	00:00:00	00:00:00
null	Total:	02:08:56	01:18:25	00:50:31	00:00:00	00:00:00	00:00:00	null	0	00:00:00	00:00:16

What is the most efficient way to get the data into a meaningful table to extract the operational stats for each agents profile per date?

Any help you can provide will be greatly appreciated. 

Thanks

[Omid_Motamedise]

This is exactly the awnser of your question

https://youtu.be/pofthD7-YuQ?si=rh_pW-ZYYlxhBo--

[Jacb]

Thank you for your link to your video. 
Your video helped me to understand the methodology.   My source was not an originally table though as it was table contain pages and tables but I still understood your methodology and how it worked. 
Thanks again for all your help 
JB

[dufoq3]

Hi @Jacb, another solution:

Output (first few columns)

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("1VVNb9QwEP0r1p5AqlR/xNlkbgscWERBgtyWHgyxlgg3Qd0sH/+eN46lZNtQtd3uoUkkP4/tefb4zWSzWbzvtmLdLs4i+LjvB9S0X/C+2V+7vul4dPXLNcF9DV5U6H3ywbudr2NnHFq34rUL4XDK1DjFF75unKj+/vTosHUn3rq2Dr6OPreiElUnVu3ut7/m6e7PgeXybLNYbX3bi3VNGFd40LT7EI5tRtcf3JVn5+86L16Fbrt7QgaZn0t1rqW2T+R06rska8lyQFRGJie7BJQZZYpkztCQsaQihAlzJUNJ8fsvfLEK4SWb5mYYFblBmMEfu1YFKQPvA6FekiwT1GkbkrmlOZpb2cid4lB1vQt8b7IgfCpugIMA4uHESpK9F2vyeNeBpyos8ZxMhRdNCI0Xn6+a/vszESLiLDUHCmLQCDlUx+EDhOqyG0Gdtz5cDICRGzrDTRdDEmhLxiS5Z0kSk4Xz1sdz5+wvqg3QlCTVMMUgHctbC+etj+S+lQTYCOpBcWPJvPXBSZBID0txPPgpS7H79uOZZABKsUENjEqAyMohGaA3VGWd7pzrZYIWYji+FKOwRxVq0ki5ImVfnkoxKDCwHGE++lAnU6HmUmzTHwd/Bm3HE9+L9S4V8oEv/wE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t, Column5 = _t, Column6 = _t, Column7 = _t, Column8 = _t, Column9 = _t, Column10 = _t, Column11 = _t, Column12 = _t]),
    ReplacedNulls = Table.TransformColumns(Source, {}, each List.ReplaceMatchingItems({_}, {{"null", null}}){0}),
    PromotedHeader = Table.PromoteHeaders(ReplacedNulls),
    TransformedHeader = Table.TransformColumnNames(PromotedHeader, each Text.Combine(List.RemoveMatchingItems(Text.Split(_, "#(lf)"), {""}), " ") ),
    GroupedRows = Table.Group(TransformedHeader, "Log In", {{"T", each 
        [ a = Table.RemoveLastN(_, 1),
          b = Table.FromRows(List.Transform(List.Skip(Table.ToRows(a), 3), (x)=> {a{0}[Log Out], a{1}[Log Out], a{2}[Log In]} & x), {"Agent Id", "Agent Name", "Date"} & Table.ColumnNames(a) )
        ][b], type table}}, 0,
        (x,y)=> Byte.From( y = "Agent Id:" ) ),
    CombinedT = Table.Combine(GroupedRows[T])
in
    CombinedT

[PwerQueryKees · Accepted solution]

Use this

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    #"Filtered Rows" = Table.SelectRows(#"Promoted Headers", each [Log In] <> null),
    #"Added Agent Id" = Table.AddColumn(#"Filtered Rows", "Agent Id", each if Text.StartsWith(Text.From([Log In]),"Agent Id",Comparer.OrdinalIgnoreCase) then [Log Out] else null),
    #"Added Agent Name" = Table.AddColumn(#"Added Agent Id", "Agent Name", each if Text.StartsWith(Text.From([Log In]),"Agent Name",Comparer.OrdinalIgnoreCase) then [Log Out] else null),
    #"Added Date" = Table.AddColumn(#"Added Agent Name", "Date", each if [Log Out] = null then [Log In] else null),
    #"Filled Down" = Table.FillDown(#"Added Date",{"Agent Id", "Agent Name", "Date"}),
    #"Filtered Data Rows Only" = Table.SelectRows(#"Filled Down", each [Log In] <> "Agent Id:"and [Log In] <> "Agent Name:"and [Log Out] <> null),
    #"Reordered Columns" = Table.ReorderColumns(#"Filtered Data Rows Only",{"Agent Id", "Agent Name", "Date", "Log In", "Log Out", "Login", "Available T", "Released T", "Available", "Released", "In Call T", "Media Type", "Calls", "Avg T To", "Max T To"})
in
    #"Reordered Columns"

to produce this:

from this Excel Data:

[Jacb]

Thank you - this worked! 
I just had to follow your steps as my source was a PDF document but I was still able to use the other steps and it worked. 
Thanks again 
JB

[PwerQueryKees]

It is always a bit of trial and error.

Here Iwould probaly

• start by removing empty lines.
• Then Transpose
• Creeate 3 new columns for AgentId, Agent Name and the data
  Using an if expression to do that only for the lines where they actually occur
• Then do a fill down on these columns
• Then remove the AgentId, name and Date rows

This would be a good start I think...

Did I answer your question? Then please mark my post as the solution and make it easier to find for others having a similar problem.
If I helped you, please click on the Thumbs Up to give Kudos.

Kees Stolker

A big fan of Power Query and Excel