My Accounting Tricks | Way to Smart Accounting

How to Break Line in Power Query | Custom Column

2023-10-15T19:28:00.008+05:30

Line breaks text in Power Query M language can be an important step. When you want to show text by Line Break in a custom column by combining other column text after applying the line break. This step is a little tricky. Please find the below step-by-step tutorial.

Text.Combine is the M language function which is very similar to CONCATENATE. The CONCATENATE function is widely used in Excel and DAX to merge the text among two or multiple columns.

The Syntax of Text.Combine is :

Text.Combine(texts as list, optional separator as nullable text) as text

How to break line in Power Query

By following the same process, users can CONCATENATE with Line Break two or more Excel columns with Power Query inside Excel.

Original data format I have

In the Power BI visual I have to show

If you use Test.Combine in Power Query Custom Column, it will show first row's result as below

I used to combine multiple-column data with M Query Text.Combine and apply the line break in Power Query

Step to Break Line in Power Query Custom Column

Step 1

Add Column (menu bar) > Custom Column

Text.Combine({[Week],"Job: "&[Job],"Job Description: "&[Job Description]}, "#")

Step 2

Now I am replaced the text delimiter "#" to "#(lf)" and see the magic

Text.Combine({[Week],"Job: "&[Job],"Job Description: "&[Job Description]}, "#(lf)")

Note - I am using small L, it is not capital I or small i

Please use the code and let me know if any questions or feedback you have.

POWER QUERY Tutorial Guide | Full Example 35 Tricks | PART-1

2020-09-26T12:40:00.006+05:30

What is POWER QUERY

Power Query is an automatic Engine introduce by Microsoft Corporation that write every step users do in Power Query and run the same steps when users refresh data. So users no need to do same steps everyday. Power Query is inbuild application in POWER BI and Excel 365, 2019, 2016. For Excel 2013 & 2010 users require to free download Power Query Addon package. Therefore, Power Query makes your daily excel work more FASTER SIMPLE EASY and AUTOMATIC.

35 Full Example

FIX Excel Incorrect Date Format | DD/MM/YYYY | MM/DD/YYYY

2020-09-24T16:13:00.009+05:30

What is Incorrect Date Format

Many times Excel would not recognize the date format like DD/MM/YYYY or DD-MMM-YYYY etc. Often excel unable to convert data into date format & produces an error. There are three main reasons, First, when users insert Date & not maintain proper format. Second, when data downloaded from ERP or Web, and the date not formatted properly. The third reason is when users convert any TEXT or CSV file to Excel, due to different file formats and conversion, there is a problem in the Date format.

Example of INCORRECT DATE FORMAT

[message]

##bullhorn## EXCEL FIND, RIGHT, LEFT, MID FUNCTION

I always use the EXCEL FIND, RIGHT, LEFT, and MID functions to solve the incorrect date format errors. Find How to use below:-

FIND function

Find function is used to get the number of position of the text which users want to find. For example, if users find for "T" in the word "ACCOUNTING", the FIND FUNCTION will return the 7.

RIGHT Function

Excel Right function will return the last character or characters or numbers from the last of the string based on the number specified in the RIGHT syntax. For example, if users want to get the last two digits of the "ACCOUNTING" word, it will return "NG".

LEFT Function

Excel Left function will return the first character or characters or numbers from the first of the string based on the number specified in the LEFT syntax. For example, if users want to get the FIRST two digits of the "ACCOUNTING" word, it will return "AC".

MID Function

Excel Left function will return the character or characters or numbers from the string based on the number specified in the MID syntax and number counting position specified in the syntax. For example, if users want to get the 5 digits from the 3RD positions of the "ACCOUNTING" word, it will return "COUNT".

COMBINE RIGHT, LEFT, MID & FIND function

STEP #1. EXTRACT YEAR

Use the LEFT function and first extract year, if the year is the left side of the string.

Use the RIGHT function and extract year, if the year is the right side of the string.

STEP #2. EXTRACT MONTH

STEP #3. EXTRACT DAY

STEP #4. COMBINE LEFT, RIGHT & MID FUNCTION WITH DATE FUNCTION

STEP #5. COMBINE LEFT, RIGHT, MID, FIND & DATE FUNCTION

STEP #6. UPDATE ANY DATE FORMAT WITH TEXT FUNCTION

What is Company | How to create Company in SAP

2020-09-06T15:21:00.000+05:30

[message]

What is COMPANY

COMPANY is the Organization Unit or Group-Company which is the parent of one or more than one company, known as COMPANY-CODE. Consolidated financial report prepares and maintains at the Group-COMPANY level. In simple words, the Company defines a Group-Company. Example TATA Group, TOYOTA Group. We Create Company as per Country, Segment, Business Type, Geographical position-wise, and can be assigned to GROUP COMPANY for consolidation reporting purposes.

Details of Create Company

T-CODE is OX15

Example - Country/Location Wise & Segment Wise

Group-Company is TOYOTA GROUP and there are country wise companies like TOYOTA UK, TOYOTA USA, TOYOTA CANADA, TOYOTA INDIA.

All Country-wise companies can assign to the Parent TOYOTA Group for consolidation.

TATA Steel, TATA Motors is the example of Business Type wise segmentation.

POINTS TO REMEMBER

[message]

##bullhorn## POINTS TO REMEMBER

SAP allows us to assign Company-Code to Group-Company if CHART OF ACCOUNT (COA) and FISCAL YEAR are the same for all Company-Code. Currency may be different between Company-Code.

How to Create Company - Step by Step

STEP #1 - Login to SAP Environment

STEP #2 - Insert SPRO in the T-Code box and press enter. You can also click the entry icon, operation both are the same.

Insert T-Code OX15 in the comment box and press enter in a keyboard or click the entry sign. It will directly move the Create Company screen.

[message]

SUGGESTION

Do not use to code when you are learning. Please use the navigation menu path, so you can look at other menu and familiar with the navigation path.

STEP #3 - Click on SAP REFERENCE IMG (configuration are done into this option)

STEP #4 - Click on Drop Down arrow as follow Enterprise Structure > Definition > Financial Accounting > Define Company

Click on the Execute icon.

STEP #5 - Click New Entries for creation. Also you can search, edit here by click the icon on this screen.

STEP #6 - Creation window will appear, fill-up the form. I used sample data for the demonstration.

COMPANY - Maximum 6 Digit code

NAME - Insert Group-Company full name

NAME OF THE COMPANY2 - Old name or alias name

STREET - Insert Full Address

PO BOX - Insert PO BOX

POSTAL CODE - Insert Address PIN Code

CITY - Insert City

COUNTRY - 2 Digit Code of Country. Click on small Icon to find the country.

LANGUAGE KEY - 2 Digit Code of Primary language.

CURRENCY - 2 Digit Code of Reporting currency.

Click on Save icon.

STEP #7 - Transport request pop-up screen will appear.

Transport Request Number & its Importance

[message]

##bullhorn## IMPORTANT LINK

What is the Transport Request Number? Why it is important? If a transport request pops up not generated automatically, how to solve? Find the below link.

Click on New to generate Transport Request

STEP #8 - Enter the name, process is Create Company, so the most useful name is "Create Company ..... ", therefore I use the name as below. So I can identify the transport request number with this name.

STEP #9 - Save the transport request number in excel or your database. Then click on the Continue tick icon.

STEP #10 - You will get the message "DATA IS SAVED"

[message]

SUGGESTION

Do not use to code when you are learning. Please use the navigation menu path, so you can look at other menu and familiar with the navigation path.

POWER QUERY : POWER BI : Connect Google Drive Sheet (3 WAY)

2020-07-31T16:50:00.005+05:30

WHAT IS GOOGLE DRIVE TO POWER BI AND POWER QUERY?

POWER BI and POWER QUERY provides several types of data connection procedures to pull data into the POWER QUERY EDITOR, connection between Google Drive and Power Query is one process of them. Users can import/pull data from GOOGLE DRIVE files to Power Query Editor, process the data, and create a visualization in POWER BI or create a report on EXCEL.

POWER QUERY is a common platform for both POWER BI and EXCEL.

Many times, this question comes to me "HOW TO IMPORT DATA FROM GOOGLE DRIVE TO POWER BI?"

In this tutorial, we will get details

Connection Between Google Drive and Power Query (for POWER BI)
Connection Between Google Drive and Power Query (for MS EXCEL)
Create Google Drive File Download Link
Synchronize Google Drive to Computer or Gateway

[message]

##check## Find POWER QUERY Tutorial List

POWER QUERY Step by Step Beginner Guide Link

PROCESS 1 - SHARE THE FILE

STEP 1 - Login Google Drive

STEP 2 - Right click the file you want to import data and click on SHARE. Share the file to anyone. Then click on Done.

[lock]

STEP 3 - You will copy the link like below

https://drive.google.com/file/d/1FM3HMx7mIzdAFLGuIOfpnLLIDsNoaLZa/view?usp=sharing

The Yellow highlighted area is your file id.

[/lock]

You need to replace the file ID in the below link

https://drive.google.com/uc?export=download&id=YOUR_FILE_ID

After replacing your final code, you will get a link like below

[https://drive.google.com/uc?export=download&id=1FM3HMx7mIzdAFLGuIOfpnLLIDsNoaLZa]

STEP 4 - Copy the link. Go to POWER QUERY window in POWER BI or in EXCEL

(How you find POWER QUERY Editor in POWER BI & EXCEL 2010 to OFFICE 365)

STEP 5 - Select Data Source from WEB

STEP 6 - Paste the Link (created in step 5) and click OK

STEP 7 - If ask for the credential, you can use ANONYMOUS or WINDOWS PASSWORD. Regarding other options, I will make another article on CREDENTIAL.

Message

Please be sure that, you done EMAIL NEWSLETTER subscription. So you will get notification for every article.

STEP 8 - Click on Sheet and click the Ok. You will find that data from GOOGLE DRIVE FILE or GOOGLE SHEET will be populated in the POWER QUERY window. After this step, you can populate data in your POWER BI or EXCEL.

PROCESS 2 - PUBLISH THE FILE

STEP 1 - Open the file in Google Sheet

STEP 2 - Go to file and click on PUBLISH ON WEB option

STEP 3 - Select the option as below image and click on Publish

STEP 4 - Copy the link

FOLLOW SAME STEP AS DESCRIBE PROCESS 1

STEP 5 - SAME AS PROCESS 1

STEP 6 - SAME BUT USE NEW LINK (which is created in Process 2 - Step 4)

STEP 7 - SAME AS PROCESS 1

STEP 8 - SAME AS PROCESS 1

PROCESS 3 - SYNC GOOGLE DRIVE FILE

I suggest choose Process 3 and it is highly secured because users do not require to share your GOOGLE DRIVE FILE or GOOGLE SHEET with anyone and publish to the web. If you choose Process 1 or Process 2, it may create a problem for confidential data because users sharing the file publicly.

Where Process 3 is completely secure and it is a quick process to import data into power query. You can uninstall and stop sync anytime you want.

STEP 1 - Go to https://support.google.com/a/answer/7491144?hl=en

You can read the instruction written over the webpage

STEP 2 - Find the WINDOWS or MAC section and download the software (which is applicable). I am using WINDOWS, so let's download the software.

After download, install the software. The installation process is common as usual.

STEP 3 - After Installation, Run the Google Drive Stream file and you will find the one Drive created in your computer.

STEP 4 - Now, You can use Google Drive as your Hard Disk Drive.

Now you can select the Source file as EXCEL and fetch/import data directly from the GOOGLE DRIVE or GOOGLE SHEET.

Select DATA SOURCE as EXCEL, not WEB

STEP 5 - Select GOOGLE DRIVE file from your local hard drive and click on IMPORT

STEP 6 - You will find data load or import to POWER QUERY

STEP 7 - Complete Data Loaded to the POWER QUERY. Now users can load the data into POWER BI or EXCEL.

STEP 8 - If you checking the source of the data, it will show data loaded from Hard Disk Drive where actual data are store in the GOOGLE DRIVE

[message]

##bullhorn## Is This Article HELPFUL

Please comment YES or NO in below comment box. Your feedback is important, please share your Feedback, Question, Idea in below comment box
If this article helpful, please find the share option below and share with your friends. You can copy the below link and paste it in Whatsapp, Facebook, Twitter, Linkedin & other social network site.
Please CLICK HERE to subscribe our newsletter and you will get notification of all update and new articles.

What is POWER QUERY and its benefits

2020-07-26T00:08:00.007+05:30

What is POWER QUERY

Power Query is a automatic Engine introduce by Microsoft Corporation which worked along with Microsoft Excel and Microsoft Power BI. It is inbuilt engine which automate everyday repeated excel work into a automatic process which can perform schedule action every time when users want to refresh data.

For example, if you working in ERP Software and try to generate report, when you press generate, one SQL Query will execute to fetch your require data from database and present to you as format mentioned in SQL Query.

In POWER QUERY, users can import file from various DATA SOURCE, perform DATA CLEANING, DATA TRANSFORMATION, DATA ARRANGING, DATA FORMATTING, make CALCULATION and prepare require data. What you done in POWER QUERY from first to end, Power Query automatically record each and every ACTION or STEP in directory. Power Query repeat all action or step, when users click on REFRESH button in future.

Therefore users perform activities one time and will enjoy POWER QUERY perform the task automatically when users want to REFRESH data.

Benefits of POWER QUERY

IMPORT or PULL DATA FROM UNLIMITED SOURCE

Users can set various source data connection for pooling or import data into POWER QUERY

DATA PREPARATION AND CALCULATION

Data Cleaning, Data Arranging, Calculation, Combine Data, Table Marge, Table Split, Data Split, Add Manual Data, Transpose Complex Data and many activities are possible to do in POWER QUERY

Find - STEP BY STEP POWER QUERY TUTORIAL LIST

AUTO RECORD TASK

What task you perform POWER QUERY Automatically record step in directory and save forever.

MARGE MULTIPLE TABLE TOGETHER and SPLIT TABLE

POWER QUERY allows two option MARGE and COMBINE to many table data together. Also users can created table with reference of one table or split table into multiple as per requirement.

AUTO PREPARE DATA WITH REFRESH OPTION

Once done, POWER QUERY perform steps when users click on REFRESH. Therefore it will automate your everyday complex work and provide or return processed data within few minutes by single mouse click.

LOAD or EXPORT DATA FROM POWER QUERY to EXCEL

When press and load data, POWER QUERY will return your data in your excel file. This way users can automate time consuming complex excel task through POWER QUERY

6. BIG Advantage of the POWER QUERY is, users can use POWER QUERY for EXCEL also POWER QUERY is available in POWER BI Visualization.

How to Install & Use POWER QUERY

For Office 2010 and Office 2013 users, Microsoft provides free Add-on for download and install in Excel. Please find the steps to install POWER QUERY

HOW TO INSTALL POWER QUERY IN EXCEL 2010 and 2013

For Office 2016, Office 2019 and Microsoft 365, POWER QUERY inbuilt in DATA menu

Find Power Query Editor in Excel 2010 2013 2016 2019 Office 365

Get Power Query Editor in Excel 365, 2019,2016,2013,2010

2020-07-24T23:41:00.003+05:30

Hope are already saw previous articles on POWER QUERY, if not please find the tutorial list for POWER QUERY

https://www.myaccountingtricks.com/2020/07/power-query-tutorial-list.html

Find POWER QUERY editor in EXCEL 2010 and EXCEL 2013

POWER QUERY interface is same for both Excel 2010 and Excel 2013. Before use POWER QUERY in EXCEL 2010 and EXCEL 2013, users need to install free add-on software provided by Microsoft.

Please find the installation and activation process

STEP 1

STEP 2

Find POWER QUERY editor in EXCEL 2016

STEP 1

STEP 2

Find POWER QUERY in EXCEL 2019 and OFFICE 365 EXCEL APP

STEP 1

STEP 2

[message]

##bullhorn## Is This Article HELPFUL

Please comment YES or NO in below comment box. Your feedback is important, please share your Feedback, Question, Idea in below comment box

POWER QUERY Step by Step Tutorial - Introduction to Advance

2020-07-15T18:42:00.006+05:30

INTRODUCTION

What is POWER QUERY and its benefits (with Video)

https://www.myaccountingtricks.com/2020/07/what-is-power-query-benefits.html

TUTORIAL 1

Activate Power Query in Excel 2010, 2013, 2016 and Office 365

https://www.myaccountingtricks.com/2020/06/power-query-power-pivot-download-activate.html

TUTORIAL 2

Find Power Query Editor in Excel 2010 2013 2016 2019 Office 365

https://www.myaccountingtricks.com/2020/07/install-power-query-excel-office-365.html

TUTORIAL 3

3 Way to Connect Google Drive Sheet in POWER QUERY and POWER BI (with Video)

https://www.myaccountingtricks.com/2020/07/power-bi-query-import-from-google-drive.html

TUTORIAL (number will arrange later)

How to Convert Crosstab Matrix Table to simple data list table in Excel (with Video)

https://www.myaccountingtricks.com/2020/06/convert-crosstab-matrix-excel-list-data-table-unpivot.html

TUTORIAL (number will arrange later)

M Language Marge Column Data in POWER QUERY TextCombine

https://www.myaccountingtricks.com/2020/07/M-language-combine-column-power-bi-excel.html

M Language Marge Column Data in POWER QUERY TextCombine Function

2020-07-07T01:03:00.005+05:30

There are two way to combine or marge data or value of two column into single column in POWER QUERY. First You can use ampersand option as like excel and Second process is use TEXT.COMBINE M-LANGUAGE function.

PROCESS 1 - TEXT.COMBINE M LANGUAGE

STEP 1 - Open Power Query window (in Excel or in Power BI)

STEP 2 - Load Data into Power Query

STEP 3 - Go to ADD COLUMN in menu bar and select CUSTOM COLUMN

STEP 4 - Different Syntax and example of TEXT.COMBINE M Language function

Text.Combine({"1st text","2nd text"})

If you want to add space middle of two text

=Text.Combine({"1st text","2nd text"}," ")

If you want to comma middle of two text

=Text.Combine({"1st text","2nd text"},",")

If want to combine data of two or multiple column

= Text.Combine({[1st column],[2nd column]},",")

If want to combine data from column and manual data, syntax will be

= Text.Combine({[1st column],[2nd column],"Manual Text"},",")

Let see one example

STEP 5 - Use TEXT.COMBINE to custom and unique output

PROCESS 2 - USE AMPERSAND OPTION TO MARGE COLUMN DATA

STEP 1 - Click on ADD COLUMN in menu bar and click on CUSTOM COLUMN

STEP 2 - Using Ampersand, same like excel.

Please find the tutorial how to use ampersand in excel

https://www.myaccountingtricks.com/2020/06/concatenate-marge-combine-data-excel-spreadsheet.html

STEP 3 - By using ampersand option, you will get beautiful merged data

Please find the video

SOLVED - Power Query and Power Pivot option not showing

2020-07-01T11:47:00.004+05:30

TONI trying to learn about POWER QUERY and POWER PIVOT, but he is unable to find this two option in the EXCEL 2013 in Ribbon and also the option not available in Excel Ribbon customization and add in.

He asked to excel experts TUTINA, how he can activate the POWER QUERY and POWER PIVOT.

TUTINA explained that if POWER QUERY and POWER PIVOT not available in your EXCEL menu bar like

INSTALL POWER QUERY IN EXCEL 2010 and EXCEL 2013

STEP 1 - Click on FILE and then click on OPTION.

STEP 2 - Click on CUSTOMIZE RIBBON and Select ALL TAB

STEP 3 - If you find POWER QUERY option, select and add POWER QUERY from LEFT side to RIGHT side and click OK.

STEP 4 - If you unable to find the option, need to download from MICROSOFT center. Visit below link

https://www.microsoft.com/en-in/download/details.aspx?id=39379

Check system requirement. Update Internet Explorer to latest version.

STEP 5 - Select appropriate software as per your windows 32 bit or 64 bit, then click on DOWNLOAD

STEP 6 - After download, install the package. Installation process same like other software installation.

STEP 7 - After installation, RESTART your excel and you will find POWER QUERY in MENU BAR.

INSTALL or ADD POWER PIVOT IN EXCEL MENU BAR

STEP 1 - Go to FILE, then select OPTION (same step like before)

STEP 2 - Click on ADD-INS

STEP 3 - Select COM ADD-INS and then press GO (as shown in above image).

STEP 4 - Select option as shown below

FINISH 5 - POWER PIVOT option added in MENU BAR

GET POWER QUERY IN EXCEL 2016, EXCEL 2019 and OFFICE EXCEL 365

Due to High Demand and Requirement, Microsoft provide POWER QUERY option in inbuilt DATA menu

3 Legit Way to Marge Text by Concatenate function in Excel

2020-06-30T21:25:00.005+05:30

TONY asked to excel expert TUTINA, HOW TO MARGE TWO or MORE EXCEL CELL VALUE INTO ONE CELL? .

TUTINA replied there are 3 simple way to marge any number of cell value into one, you can use AMPERSAND "&" or you can use CONCATENATE function to marge cell value. By using AMPERSAND and CONCATENATE, you can marge cell data of WPS OFFICE SPREADSHEET, OPENOFFICE SPREADSHEET, GOOGLE SPREADSHEET and others. Also by this two function, you can marge/concatenate large size of data within one minutes.

TUTINA asked to TONY to see the 3 process to marge cell data. Let start

[PROCESS 1]

Use AMPERSAND function to marge cell data in EXCEL, WPS Spreadsheet, Open Office Spreadsheet, Google Spreadsheet

STEP 1 :- Use & sign between two or three cell reference. Use " " between two cell reference for adding space between two value.

STEP 2 :- You can use & option many time to marge many cell data

STEP 3 :- You can use text between two cell data as following way. Also you can use COMMA "," sign between two value.

[PROCESS 2]

Use CONCATENATE function to marge cell data in EXCEL, WPS Spreadsheet, Open Office Spreadsheet, Google Spreadsheet.

STEP 1 :- Use CONCATENATE after the = sign and use formula as following way. In below image, no space used in between two cell value.

STEP 2 :- Use " " double quote to apply space between two value.

Convert Crosstab Matrix to flat data list table in Excel

2020-06-12T23:27:00.012+05:30

WHAT IS CONVERT CROSSTAB TABLE

Crosstab table is also known as the MATRIX table or PIVOT table. Normally we are using a tabular list flat excel table which has a header in the top row. But if one or more than one headers are present at the ROW level and also in the COLUMN level, these types of tables known as CROSSTAB or MATRIX or PIVOT table. Example of CROSSTAB or MATRIX or PIVOT table

WHAT ARE THE PROBLEM OF CROSSTAB MATRIX DATA

WHAT ARE THE SOURCE OF CROSSTAB MATRIX DATA

HOW TO CONVERT CROSSTAB MATRIX TABLE INTO TABULAR FLAT LIST DATA TABLE

Find the Sample Data below, let convert this data in Tabular Data List Table format.

STEP #1 - You need to insert a row and concatenate values.

Find the below step Find an article to use a different type of concatenating formula.

STEP #2 - Need to select Power Query, Find the below step

Find an article on POWER QUERY DATA SETTING.

STEP #3 - Remove unwanted column

STEP #4 - Select Name and Price Column with pressing "CTRL KEY"

STEP #5 - Go to TRANSFORM menu

STEP #6 - Click on "UNPIVOT OTHER COLUMN" under the "UNPIVOT" menu

STEP #7 - Rename the Column Header as per your requirement

STEP #8 - Data is ready and now time to populate data from POWER QUERY to EXCEL

STEP #9 - Data populated in excel successfully.

TUTINA explained the process by CONVERTING MATRIX or CROSSTAB or PivotTable EXCEL DATA into SIMPLE DATA TABLE and asked to TONI to convert the LARGE and COMPLEX CROSSTAB datafile which MANAGER assigned to TONI.

[message]

##check## Download project file and Try Yourself. Let me status in comment box

Please download the project file and convert it in a simple excel data file. Let me the status of your practice in the COMMENT box. If you face any problem, please let me know.

[Download ##download##] [Preview ##eye##]

PLEASE SHARE THIS POST WITH YOUR FRIENDS BY

CLICKING SHARING BUTTON BELOW.