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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
Query_query
Regular Visitor

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 into the results shown below.

 

                           
        Code123456789101112131415161718
        BranchABCDEFGHIJKLMNOPQR
        CountryAlbaniaAlgeriaAndorraAngolaAntigua and BarbudaArgentinaArmeniaAustraliaAustriaAzerbaijanBahamasBahrainBangladeshBarbadosBelarusBelgiumBelizeBenin
        CCYUSDUSDEUREURUSDUSDEUREURUSDUSDEUREURUSDUSDEUREURUSDEUR
        Rate111.21.2111.21.2111.21.2111.21.211.2
   Code2keyinfonameitemgrade                  
   1A1A1000DogAmaranth0273669027366902736690273669027366902736690273669027366902736690273669027366902736690273669027366902736690273669027366902736690
   2A2A2000DonkeyAmber1284702847028470284702847028470284702847028470284702847028470284702847028470284702847028470
   3A3A3000LeopardAmethyst2768230076823007682300768230076823007682300768230076823007682300768230076823007682300768230076823007682300768230076823007682300
   4A4A4000Polar bearApple green0824098240982409824098240982409824098240982409824098240982409824098240982409824098240982409
   5A5A5000RabbitApple red1826588265882658826588265882658826588265882658826588265882658826588265882658826588265882658

 

truncated results:

Code2keyinfonameitemgradeCodeBranchCountryCCYRateBalance
1A1A1000DogAmaranth01AAlbaniaUSD12736690

 

1 ACCEPTED SOLUTION

Hi @Query_query ,

 

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

JasperDJ_0-1692956648296.png

 

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"

 

View solution in original post

3 REPLIES 3
JasperDJ
Helper I
Helper I

Hi @Query_query ,

 

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

Before:

JasperDJ_0-1692946631891.png

 

After:

JasperDJ_1-1692946649244.png

 

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"

  

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.

Hi @Query_query ,

 

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

JasperDJ_0-1692956648296.png

 

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"

 

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Power BI Header Change

Power BI Header Update

Read about the exciting updates for the Power BI forum.

Top Solution Authors
Top Kudoed Authors