Forum Discussion
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 into the results shown below.
| Code | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 | 12 | 13 | 14 | 15 | 16 | 17 | 18 | ||||||||
| Branch | A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | ||||||||
| Country | Albania | Algeria | Andorra | Angola | Antigua and Barbuda | Argentina | Armenia | Australia | Austria | Azerbaijan | Bahamas | Bahrain | Bangladesh | Barbados | Belarus | Belgium | Belize | Benin | ||||||||
| CCY | USD | USD | EUR | EUR | USD | USD | EUR | EUR | USD | USD | EUR | EUR | USD | USD | EUR | EUR | USD | EUR | ||||||||
| Rate | 1 | 1 | 1.2 | 1.2 | 1 | 1 | 1.2 | 1.2 | 1 | 1 | 1.2 | 1.2 | 1 | 1 | 1.2 | 1.2 | 1 | 1.2 | ||||||||
| Code2 | key | info | name | item | grade | |||||||||||||||||||||
| 1 | A1 | A1000 | Dog | Amaranth | 0 | 2736690 | 2736690 | 2736690 | 2736690 | 2736690 | 2736690 | 2736690 | 2736690 | 2736690 | 2736690 | 2736690 | 2736690 | 2736690 | 2736690 | 2736690 | 2736690 | 2736690 | 2736690 | |||
| 2 | A2 | A2000 | Donkey | Amber | 1 | 28470 | 28470 | 28470 | 28470 | 28470 | 28470 | 28470 | 28470 | 28470 | 28470 | 28470 | 28470 | 28470 | 28470 | 28470 | 28470 | 28470 | 28470 | |||
| 3 | A3 | A3000 | Leopard | Amethyst | 2 | 7682300 | 7682300 | 7682300 | 7682300 | 7682300 | 7682300 | 7682300 | 7682300 | 7682300 | 7682300 | 7682300 | 7682300 | 7682300 | 7682300 | 7682300 | 7682300 | 7682300 | 7682300 | |||
| 4 | A4 | A4000 | Polar bear | Apple green | 0 | 82409 | 82409 | 82409 | 82409 | 82409 | 82409 | 82409 | 82409 | 82409 | 82409 | 82409 | 82409 | 82409 | 82409 | 82409 | 82409 | 82409 | 82409 | |||
| 5 | A5 | A5000 | Rabbit | Apple red | 1 | 82658 | 82658 | 82658 | 82658 | 82658 | 82658 | 82658 | 82658 | 82658 | 82658 | 82658 | 82658 | 82658 | 82658 | 82658 | 82658 | 82658 | 82658 |
truncated results:
| Code2 | key | info | name | item | grade | Code | Branch | Country | CCY | Rate | Balance |
| 1 | A1 | A1000 | Dog | Amaranth | 0 | 1 | A | Albania | USD | 1 | 2736690 |
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"
3 Replies
JasperDJHelper 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:
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"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
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"
