Transform Tab in Query Editor: Use First Row as Headers and Use Headers as First Row of Query Editor, rename heading and other option of right click: Lesson-7 P-8.1

IT 02.03.2024 0 Comments Add to Reading List

Right Click on Column and use first Row as Header & Header as First Row: Lesson-7 P-8.1

Right-clicking a column header in the Power BI Query Editor opens a context menu that gives you access to a number of useful modification and management options. Here's a breakdown of some of the most common actions:

Data Change:

Change Data Type: Changes the data type (text, number, date/time, etc.) of the column.
Replace Values: Replaces the specified values within the column.
Rename Column: Assigns a new name to the column.
Remove Column: Deletes the selected column from the table.
Remove Other Columns: Deletes all columns except the selected one.
Duplicate Column: Creates a new column with identical data.
Split Column: Splits a column into multiple columns based on criteria (delimitation, number of characters, etc.).
Pivot Column: Pivots data from a column across multiple columns (see earlier explanation on Pivot/Input).
Unpivot Columns: Unpivots multiple columns into rows.
Fill Down/Fill Up: Replaces empty values up or down with the next non-empty value.
etc. etc.

Calculations:

Add Custom Column: Creates a new column based on the DAX formula.
Index Column: Adds a column with auto-incrementing numbers.
Statistics: Calculates summary statistics (average, count, minimum, maximum, etc.).

Other Actions:

Move: Allows the column to be repositioned with options like "From", "To", "Before" and "After".
Remove Errors: Deletes rows with errors in the selected column.
Keep Rows / Remove Rows: Lets you filter the table based on the values in the selected column.
Group by: Groups rows based on distinct values in a column, calculating sums if needed.

Important Note:

The valid options available may vary depending on the data type of the column you select.
These actions apply changes directly to your data, so be aware of your changes and consider working on a copy of your data if needed.

Lab Work

T1:- Right Click Menu

1:- Copy

2:- Remove

Remove that column which select with Right Click (Column6)
- Right Click (on required column heading)--> Remove

Then you have view without Column6

3:- Remove Other Columns

Remove that columns which not selected
- Right Click (on required column heading) --> Remove Other Columns

Remove all other than column6

You have view only column6

4:- Duplicate Column

Create a copy of selected colum
- Right Click (on required column heading) --> Duplicate Column

Then you have

5:- Add Column from Example...

6:- Remove Duplicates

Remove duplicate values from selected column
- Select required colunm
- Right Click (on required column heading) --> Remove Duplicates

Column6 have some duplicate values

After select Remove Duplicate option from right click menu

then you have a view like

7:- Remove Errors

Remove Rows cotaining error from selected columns
- Select required colunm
- Right Click (on required column heading)--> Remove Errors

8:- Change Type

Change data type of selected column
- Select required colunm
- Right Click (on required column heading)--> Change Type -->now select required data type from list

You can also do this (change type) click on top left corner of column heading

9:- Transform (with text datatype)

Change case & other related option like trim, clean etc
- Select required colunm
- Right Click (on required column heading)--> Transform -->now select required data type from list

9a:- lowercase

change your text in lower case
Right Click (on required column heading)--> Transform -->lowercase

then you have

9b:- lowercase

change your text in upper case
Right Click (on required column heading)--> Transform -->UPPERCASE

then you have

9c:- Capitalize Each Word

change your text in standard form (first Capital and others slamm)
Right Click (on required column heading)--> Transform --> Capitalize Each Word

then you have

9d:- Trim

Delete all extra spaces in your word
Right Click (on required column heading)--> Transform -->Trim

then you have

9e:- Clean

clean your deta from errors
Right Click (on required column heading)--> Transform -->Clean

then you have

9f:- Length

display word size in char (How many alphabets in a word)
Right Click (on required column heading)--> Transform --> Length

then you have

9-P2:- Transform (with numeric datatype)

Change Round, Absolute value , factorial, Power, Square Root etc
- Select required colunm
- Right Click (on required column heading)--> Transform -->now select required option from list