transforming a report as source for powerBI

[Query_query]

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

[JasperDJ]

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]

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 · Accepted solution]

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"