Forum Discussion

Query_query's avatar
Query_query
Regular Visitor
3 years ago
Solved

transforming a report as source for powerBI

hi all   I am trying to convert the following report into a proper data structure. However, there are field headers and values in the bold portion.  I am trying to convert the original report int...
Need Help
  • JasperDJ's avatar
    JasperDJ
    3 years ago

    Hi Query_query ,

     

    I'd recommend creating a new table for the top 5 rows. It could look something like this:

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("1ZVNT+MwEIb/SsQZrZK0pOkxUHb53IUiDgj1MNnMuobEqabOAX49M55QpFyoBKzK4Rm/diaxX3/l/n4v2tv/Tyz23+/tqK2Qi4RJmREzZg6YjJkwOTOVnFiCZCaSmkhuIsmJZCeSnkh+km/V8yGB+7tkUUhFhsLMmGPmJ/OLOWFOmTPmnLlgLpnfzB/mirlm5lua7ZynJ+mzLsFZCMogqXJVS6TKtLUKb00HEbgqOgQquyq0kkF+4FQ32H+oW3uC+k2rekYqwT6AE5ewhAbWqgistjlTQ4XrZahwctWGDKyBul4Z2zWq7DMG4fjlrSwf3XG8vZlt4vHtfBM/t130NmOag3/dc4Ef6Vv8aBur4RBki8vzR5SVt+5fy4WDRsZgPcrEGoJwDL7m3MnACg1xLIdo1hqpNcBHwMu6S2M6GWXZ9DupoU+Z5ELDq0+nk140JVI/E2k+nsQ7Uw49yKVWaFAPF9iugKpgAv3yae17p5MsT0ch5buooVe5uwsN6vWKbz2KSgRZq2K1qjEyhOj6HZqn43i6M+XQjfyECg3qZg5laf3GCWHV78A8zQ7ynSkXixc=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"(blank)" = _t, #"(blank).1" = _t, #"(blank).2" = _t, #"(blank).3" = _t, #"(blank).4" = _t, #"(blank).5" = _t, #"(blank).6" = _t, #"(blank).7" = _t, #"(blank).8" = _t, #"(blank).9" = _t, #"(blank).10" = _t, #"(blank).11" = _t, #"(blank).12" = _t, #"(blank).13" = _t, #"(blank).14" = _t, #"(blank).15" = _t, #"(blank).16" = _t, #"(blank).17" = _t, #"(blank).18" = _t, #"(blank).19" = _t, #"(blank).20" = _t, #"(blank).21" = _t, #"(blank).22" = _t, #"(blank).23" = _t, #"(blank).24" = _t, #"(blank).25" = _t, #"(blank).26" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"(blank)", type text}, {"(blank).1", type text}, {"(blank).2", type text}, {"(blank).3", type text}, {"(blank).4", type text}, {"(blank).5", type text}, {"(blank).6", type text}, {"(blank).7", type text}, {"(blank).8", type text}, {"(blank).9", type text}, {"(blank).10", type text}, {"(blank).11", type text}, {"(blank).12", type text}, {"(blank).13", type text}, {"(blank).14", type text}, {"(blank).15", type text}, {"(blank).16", type text}, {"(blank).17", type text}, {"(blank).18", type text}, {"(blank).19", type text}, {"(blank).20", type text}, {"(blank).21", type text}, {"(blank).22", type text}, {"(blank).23", type text}, {"(blank).24", type text}, {"(blank).25", type text}, {"(blank).26", type text}}),
    #"Kept First Rows" = Table.FirstN(#"Changed Type",6),
    #"Removed Top Rows" = Table.Skip(#"Kept First Rows",1),
    #"Transposed Table" = Table.Transpose(#"Removed Top Rows"),
    #"Removed Top Rows1" = Table.Skip(#"Transposed Table",8),
    #"Promoted Headers" = Table.PromoteHeaders(#"Removed Top Rows1", [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Code", Int64.Type}, {"Branch", type text}, {"Country", type text}, {"CCY", type text}, {"Rate", Int64.Type}})
    in
    #"Changed Type1"

     

JasperDJ's avatar
JasperDJ
Icon for Helper I rankHelper I
3 years ago

Hi Query_query ,

 

You could try to remove the top 6-rows and promoting the 7th as headers. Would look something like this:

Before:

 

After:

 

Here is the code I used:

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("1ZVNT+MwEIb/SsQZrZK0pOkxUHb53IUiDgj1MNnMuobEqabOAX49M55QpFyoBKzK4Rm/diaxX3/l/n4v2tv/Tyz23+/tqK2Qi4RJmREzZg6YjJkwOTOVnFiCZCaSmkhuIsmJZCeSnkh+km/V8yGB+7tkUUhFhsLMmGPmJ/OLOWFOmTPmnLlgLpnfzB/mirlm5lua7ZynJ+mzLsFZCMogqXJVS6TKtLUKb00HEbgqOgQquyq0kkF+4FQ32H+oW3uC+k2rekYqwT6AE5ewhAbWqgistjlTQ4XrZahwctWGDKyBul4Z2zWq7DMG4fjlrSwf3XG8vZlt4vHtfBM/t130NmOag3/dc4Ef6Vv8aBur4RBki8vzR5SVt+5fy4WDRsZgPcrEGoJwDL7m3MnACg1xLIdo1hqpNcBHwMu6S2M6GWXZ9DupoU+Z5ELDq0+nk140JVI/E2k+nsQ7Uw49yKVWaFAPF9iugKpgAv3yae17p5MsT0ch5buooVe5uwsN6vWKbz2KSgRZq2K1qjEyhOj6HZqn43i6M+XQjfyECg3qZg5laf3GCWHV78A8zQ7ynSkXixc=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"(blank)" = _t, #"(blank).1" = _t, #"(blank).2" = _t, #"(blank).3" = _t, #"(blank).4" = _t, #"(blank).5" = _t, #"(blank).6" = _t, #"(blank).7" = _t, #"(blank).8" = _t, #"(blank).9" = _t, #"(blank).10" = _t, #"(blank).11" = _t, #"(blank).12" = _t, #"(blank).13" = _t, #"(blank).14" = _t, #"(blank).15" = _t, #"(blank).16" = _t, #"(blank).17" = _t, #"(blank).18" = _t, #"(blank).19" = _t, #"(blank).20" = _t, #"(blank).21" = _t, #"(blank).22" = _t, #"(blank).23" = _t, #"(blank).24" = _t, #"(blank).25" = _t, #"(blank).26" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"(blank)", type text}, {"(blank).1", type text}, {"(blank).2", type text}, {"(blank).3", type text}, {"(blank).4", type text}, {"(blank).5", type text}, {"(blank).6", type text}, {"(blank).7", type text}, {"(blank).8", type text}, {"(blank).9", type text}, {"(blank).10", type text}, {"(blank).11", type text}, {"(blank).12", type text}, {"(blank).13", type text}, {"(blank).14", type text}, {"(blank).15", type text}, {"(blank).16", type text}, {"(blank).17", type text}, {"(blank).18", type text}, {"(blank).19", type text}, {"(blank).20", type text}, {"(blank).21", type text}, {"(blank).22", type text}, {"(blank).23", type text}, {"(blank).24", type text}, {"(blank).25", type text}, {"(blank).26", type text}}),
#"Removed Top Rows" = Table.Skip(#"Changed Type",6),
#"Promoted Headers" = Table.PromoteHeaders(#"Removed Top Rows", [PromoteAllScalars=true]),
#"Removed Other Columns" = Table.SelectColumns(#"Promoted Headers",{"Code2", "key", "info", "name", "item", "grade", " _3"}),
#"Renamed Columns" = Table.RenameColumns(#"Removed Other Columns",{{" _3", "Balance"}}),
#"Changed Type1" = Table.TransformColumnTypes(#"Renamed Columns",{{"Code2", Int64.Type}, {"key", type text}, {"info", type text}, {"name", type text}, {"item", type text}, {"grade", Int64.Type}, {"Balance", Int64.Type}})
in
#"Changed Type1"

  

  • Query_query's avatar
    Query_query
    Regular Visitor
    3 years ago

    Thanks Jasper, the top 5 rows are required, as they will be transformed as five additional headers (code, branch, country, cny, rate) with their respective values.

    • JasperDJ's avatar
      JasperDJ
      Icon for Helper I rankHelper I
      3 years ago

      Hi Query_query ,

       

      I'd recommend creating a new table for the top 5 rows. It could look something like this:

       

      let
      Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("1ZVNT+MwEIb/SsQZrZK0pOkxUHb53IUiDgj1MNnMuobEqabOAX49M55QpFyoBKzK4Rm/diaxX3/l/n4v2tv/Tyz23+/tqK2Qi4RJmREzZg6YjJkwOTOVnFiCZCaSmkhuIsmJZCeSnkh+km/V8yGB+7tkUUhFhsLMmGPmJ/OLOWFOmTPmnLlgLpnfzB/mirlm5lua7ZynJ+mzLsFZCMogqXJVS6TKtLUKb00HEbgqOgQquyq0kkF+4FQ32H+oW3uC+k2rekYqwT6AE5ewhAbWqgistjlTQ4XrZahwctWGDKyBul4Z2zWq7DMG4fjlrSwf3XG8vZlt4vHtfBM/t130NmOag3/dc4Ef6Vv8aBur4RBki8vzR5SVt+5fy4WDRsZgPcrEGoJwDL7m3MnACg1xLIdo1hqpNcBHwMu6S2M6GWXZ9DupoU+Z5ELDq0+nk140JVI/E2k+nsQ7Uw49yKVWaFAPF9iugKpgAv3yae17p5MsT0ch5buooVe5uwsN6vWKbz2KSgRZq2K1qjEyhOj6HZqn43i6M+XQjfyECg3qZg5laf3GCWHV78A8zQ7ynSkXixc=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"(blank)" = _t, #"(blank).1" = _t, #"(blank).2" = _t, #"(blank).3" = _t, #"(blank).4" = _t, #"(blank).5" = _t, #"(blank).6" = _t, #"(blank).7" = _t, #"(blank).8" = _t, #"(blank).9" = _t, #"(blank).10" = _t, #"(blank).11" = _t, #"(blank).12" = _t, #"(blank).13" = _t, #"(blank).14" = _t, #"(blank).15" = _t, #"(blank).16" = _t, #"(blank).17" = _t, #"(blank).18" = _t, #"(blank).19" = _t, #"(blank).20" = _t, #"(blank).21" = _t, #"(blank).22" = _t, #"(blank).23" = _t, #"(blank).24" = _t, #"(blank).25" = _t, #"(blank).26" = _t]),
      #"Changed Type" = Table.TransformColumnTypes(Source,{{"(blank)", type text}, {"(blank).1", type text}, {"(blank).2", type text}, {"(blank).3", type text}, {"(blank).4", type text}, {"(blank).5", type text}, {"(blank).6", type text}, {"(blank).7", type text}, {"(blank).8", type text}, {"(blank).9", type text}, {"(blank).10", type text}, {"(blank).11", type text}, {"(blank).12", type text}, {"(blank).13", type text}, {"(blank).14", type text}, {"(blank).15", type text}, {"(blank).16", type text}, {"(blank).17", type text}, {"(blank).18", type text}, {"(blank).19", type text}, {"(blank).20", type text}, {"(blank).21", type text}, {"(blank).22", type text}, {"(blank).23", type text}, {"(blank).24", type text}, {"(blank).25", type text}, {"(blank).26", type text}}),
      #"Kept First Rows" = Table.FirstN(#"Changed Type",6),
      #"Removed Top Rows" = Table.Skip(#"Kept First Rows",1),
      #"Transposed Table" = Table.Transpose(#"Removed Top Rows"),
      #"Removed Top Rows1" = Table.Skip(#"Transposed Table",8),
      #"Promoted Headers" = Table.PromoteHeaders(#"Removed Top Rows1", [PromoteAllScalars=true]),
      #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Code", Int64.Type}, {"Branch", type text}, {"Country", type text}, {"CCY", type text}, {"Rate", Int64.Type}})
      in
      #"Changed Type1"