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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
Fabcon_Europe_Social_Bogo

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors