Clean and Transform Data using M Code Stored in Text File

One of the amazing functionalities in PowerCredit is the possibility to transform data using M code stored in flat file

In this video, I'm going to show you how to perform data transformation in PowerCredit using mcode in text file

Therefore, let's get started. Before we dive into this, let's explain the sample data

This is a sales data and of course we can see the data in the data. can see the data is displayed horizontally. So our goal is to transform the data to display

the data vertically. Now in row number one we have what is called the Ethereum number that

are unique to each data values. In row number two we have the list of transaction for the product

and in number three we have the list of the payment time in row number four, the units

number five the price and number six. the sales amount. Now our goal is to transport this data, perform some cleanups and then use the code in the

advanced editor stored in a text file to clean up the same data set

So let's dive into this demonstration. Now the first thing I'm going to do is to apply table or format the data as an Excel table

I can use the two keyboard short code, Control T or Control L

Now Control L also brings up the critical. table dialog box now by default my table is checked automatically so my table

has error so I'm going to uncheck that and then click okay now the data is now

formatted stored in an Excel table and of course we can see column 1 to column

1,0782 this simply means we have 1,0782 records of transaction so what I'm

What I'm going to do next is to right click and choose Get Data from Table slash Range

Which is going to launch the PowerCode Editor because the data is officially stored in an Excel table by pressing Control L

Alternatively I can come to the Data tab under the Getter Transform Data Group I can choose from Table slash range and that going to launch the power code editor with the table in the editor and there we go so the data is now officially in the

power code editor now what i'm going to do next is to come to the transform tab under the table

i'm going to choose transpose and there we go so the data has been transported from the horizontal display

to vertical layout and what is super cool now the next I want to do is to apply the

right data types now the first column column one is actually showing the

cedar number that are unique to each of the date values so I'm going to right click

and choose change time or I can even come to this little icon and choose date and

there we go the data is now transformed to the proper date data type so I can rename and

this date click enter to commit and for the text field this is going to be the product and then

we have the payment type so select by clicking down the shift key and then column 3 right click

and I want to choose the text data time and then I can rename this as products and then

rename as payment time and click enter to commit and For the other three columns, this is going to be a whole number data type

So all number and then I can rename and call this unit

Hold on the shift key, select the column C's, right click, change time and this will be currency

and then we can rename individually and call this price. And then the column C is this going to be the sales amount

Then click enter. Now we're going to maintain the same. table one because based on what I discover if this name changes the code will not work if I want to try to use that in the

Evaluate Expression. Evaluate DAC functions. I'm going to maintain the same table one which is very important

So I going to come to the view tab and then click on the advanced editor so we can see all the code here You can even scroll that we have bunch of code okay so i going to just press control a to select all the code

control c to copy into the clipboard and then let's open a new note bar control v and there we have

the code and i'm going to choose file and save as let's just call this one code

and then i'm going to save into this my code folder in the music environment so click on save

i can click on the close the note pad i can close the advanced editor and i'm going to discard

the power query changes so just discard and i'm going to close the excel one book i'm not going to

save anything and i can come to the my code and we have the code in the note part so i can actually

investigate there goes where everything is working fine So I'm going to go back and reopen the Excel will double click and I'm going to apply the same

control T to format as an Excel table, uncheck my table as errors, and then click okay

And of course we maintain the same table one which is super important for this operation to work

Now what I'm going to do is come to the data tab under the get data

I want to come to from other sources and I want to choose a blank

query because we have to fire that from a blank query using the advanced editor

So let's see the freshly editor coming up and there we go

So we have created one just a blank query. Now I'm going to come to the view tab and then click on the advanced editor and then we have this code

Basically I'm going to delete this. I can retain this name. So the first I want to use the text dot from binary M function

So text dot from binary. Now when you click on this little icon, you can see the definition it's the code's data from a binary form into text

So I'm going to press the tab key to select and then open the brackets

Now for the binary, I'm going to use the file.com contents to access the content from my C drive

So file.com content and this will return the content of the specified file as binary

So press the tab key and then open the parentheses and insert double code Now I going to go to my c drive that particular location where the file is stored so i going to come back here and then i going to double

click this my code and i want to access this you know extension now basically is going to show me my

code but i need a backward slash to access the name of the file which is code.t.x which is very

important when i click on that this opens the notepad so just close the

and then click back into this environment control scene and this exactly what we needed and I'm going to come to the Excel the power of query and inside the double code control the interface and then just put in a comma and click enter now next thing we need to do is to use the expression

to evaluate to evaluate the source and then we can access the content so I can just give this you know a name let's just call it in code cell or anything like you can use this just a little

equals now we're going to use the expression dot evaluates now this M function

returns the result of evaluating an end code it's going to be the source so

press the tag key and open the bracket now for the document I'm going to pass in

this in a source and control the comma now for the environment I'm just going to

use the ash sign share and that's all I need to do just copy this code cell and

pass that into the end function so control v to paste and this is all the code we need to do

so basically the source equals to text dot from binary and instead i will pass we use the file

content and then we pass in the extension from my c drive and then the user my name

inside my music and then this folder and then we have this code dot txt and then we just use a label

and then we use the expression that is very important and then we're just passing the same label so just go ahead and click okay and this is the moment

of truth there we go this is amazing so we can see we have the data inside the power

could it transform which is super cool hope and enjoy this video if you do like share with your

friends and comment thank you and bye for now cheers