Hello Folks!!
This time I came up with some tricks related to the transformation of one format of data into another via M-Query that will be readable friendly with Power BI.
Few day back, I was working with Power BI Desktop in one of the important report of my Day-In Day-Out work and encounter a very interesting piece of work to deal with, there in, data is available in one of the source file lets say EXCEL or CSV and data is not amicable with Power BI, i.e. Power BI may not be able to render this piece of data with such available format correctly, data could be like the way it is shown in the snapshot below, termed it as table A.
Basically, I am going to elaborate here very practical scenario where we need to transform data of one table which having huge data into somewhat readable format by Power BI, but as a pre-requisite, there must be some symmetry in terms of data availability in source big table, and here symmetry is that we have fixed number of data rows after every Data Head.
Let's start the road roller with some illustration.
Requirement :
As stated above as well, we have one source file where data is not available as normal as available in tabular data, instead, we have one row that having information of header, for e.g. DATA, CLOUD and AZURE and there after, there are rows corresponding to particular head, as one can see there is always a two rows for every head (here in this post, I am only dealing with the scenario where number of rows for each head is same, as in this scenario we have two rows for each DATA, CLOUD and AZURE), now we need to load this data into Power BI Dataset which can be easily read by Power BI and report will be build on that set of the data.
Resolution :
First of all, let me explain the source file so that it will be easy to understand what need to be done and what exact transformation we needed to create.
To start with , source file (termed as TABLE A) contains very first row as "ColA, ColB and ColC, and then after rows contains string text of DATA for all the columns, and there after, there are two rows of data beneath data row which means these two rows belongs to DATA only, similarly, we have next two where all columns having string Text as "CLOUD: and beneath it, we have two more rows, again it means it belongs to CLOUD only, and then onward we have similar set of data for another heads such as "AZURE", here number of heads can be any but number of data rows for each head must be 2 as in this case, however, it can 3 or 4 or any, but limitation here is, there must be same number of rows for each head.
Till here, we talked a lot about the source file, how and what type of data it contains, now its time to elaborate on how source file need to transform into another layout which is very well understood by Power BI.
To transform the file into the format which Power BI can well understand, we need to transform the above file as shown as below, i.e. Table A to Table B where Table B is the new transformed format which Power BI can read and able to create report based on it.
i.e. all the category data will come under one column, lets say name of the column is Source and rest will go under specific column as previous i.e. ColA, ColB and ColC.
Let's see how we can transform the file with the help of M-Query of Power BI, illustrated and detailed in below mentioned steps.
Step 1 : Open Power BI Desktop
Step 2 : Navigate ribbon to the Home and Click on Get Data
Step 3 : Under Get Data, search for Blank Query
Step 4 : On opened Blank Query, paste the below mentioned M-Query and provide meaningful name such as TransformTable and click on OK, this will create Function TransformTable in Power BI platform and scope is limited to the complete report level.
* Below is the M-Query for the Function : TransformTable
Function : TransformTable
(x) as table =>
let
#"Save Header Name" = {x{0}[ColA]},
#"Add New Column" = Table.AddColumn(x, "Source", each #"Save Header Name"{0} ),
#"Removed Top Rows" = Table.Skip(#"Add New Column",1),
#"Reordered Columns" = Table.ReorderColumns(#"Removed Top Rows",{"Source", "ColA", "ColB", "ColC"})
in #"Reordered Columns"
* Below is the look and feel of the function TransformTable (in the Query Editor of Power BI) created.
Step 5 : Navigate ribbon to the Home and Click on Get Data
Step 6 : Under Get Data, search for Blank Query
Step 7 : On opened Blank Query, paste the below mentioned M-Query and provide meaningful name such as "MyData" and click on OK, this will create MyData table in Power BI platform and scope is limited to the complete report level.
M Query for Table : MyData
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WckksSVTSQaFidaKVfHQNgVwjIDaG8kFsUyA2A/Odc/JLU4A8VBqi0hjItwBiSyjfBMg2BBlnaAQWcawqLUoFclFpiFqQDYZgDaZQETMQxxxEWCjFxgIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ColA = _t, ColB = _t, ColC = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"ColA", type text}, {"ColB", type text}, {"ColC", type text}}),
#"Split Tables" = Table.Split( #"Changed Type", 3 ),
#"Transform Table" = List.Transform(#"Split Tables", TransformTable),
#"Load Table" = Table.Combine( #"Transform Table" )
in
#"Load Table"
* Highlighted Yellow - Highlighted section referring to the source for the table, please replace this highlighted section with the your source of code.
* Highlighted (RED) - This section of code will split table into number of rows, i.e. "3" here will define as with how many number of rows of data does table split with. for e.g. here say for DATA we have three rows, two data row and one DATA row itself and so on for the rest of the data, below is the output of the transformed data
* So, if the number of rows are say 5 including Category for one Category, then 3 in a code will be replaced by 5.
Step 8 : Click on OK.
* Once OK was clicked, data will get refreshed and look like as below.
Hurray !!! Here we go, we got the desired result.
As rule of thumb everything in this world having pros and cons, this is also not exception, please check below for the same.
Pros :
1. Transformed data is easy to understand and successfully got processed in Power BI.
2. No external tool is required to change the format of the data. M-Query does all the magic.
Cons :
1. Number of columns must be fixed.
2. Number of data rows for every heads must be same, for e.g. here, there are two rows for each DATA, CLOUD and AZURE etc category.
Author - Feel free to ask query if you have any query
