Forum Discussion

Jacb's avatar
Jacb
Frequent Visitor
1 year ago
Solved

How to deal with multiple headers in data in Power BI

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 iden...
Need Help
  • PwerQueryKees's avatar
    PwerQueryKees
    1 year ago

    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:

     

     

dufoq3's avatar
dufoq3
Icon for Community Champion rankCommunity Champion
1 year ago

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