CODEWITHSUNDEEP
powerbipowerquerypowerpivot
Stuck At Home Overflow
Stuck At Home Overflow

Reputation: 61

Power Query / Power BI - How to move a cell value to a separate cell the easiest way?

I want to move a single value from column B to column A, how can I achieve it in the most simplest way in Power Query / Query Editor (Power BI)?

Please see attached images.

enter image description here

enter image description here

I know I might need to declare a variable so please enlighten me. By the way, I will delete row 1 afterwards, promote my headers, and rename column2 as PERIOD.

Thank you.

Upvotes: 1

Views: 2884

Answers (1)

Marc Pincince
Marc Pincince

Reputation: 5202

This might be along the lines of what you want to do.

If I start with this table named as Table1:

enter image description here

Then I click on the fx to the left of the formula bar:

enter image description here

And type = Table.InsertRows(Source, Table.RowCount(Source), {[Column2 = Source[KP20 rate]{0}, KP20 rate = null, Column4 = null]}) into the formula bar:

enter image description here

I used Table.InsertRows to create a new row in Table1. Source is the name of the latest state of Table1 after it is pulled into Power Query and before I do this step. So I actually use Source as the name of the table for this step instead of Table1. (Each applied step basically results in its own table. You probably know this already, but others may not.) So for this step I use Source as the table name in the Table.InsertRows statement. Then, since I want the new row to appear at the bottom of Source, I just enter the Table.RowCount of Source as the row number location for the new row. Then I enter each of the Columns' names and their values to be added. For Column2, I entered the value "Source[KP20 rate]{0}." Source[KP20 rate]{0} basically treats column KP20 rate as a list, where {0} serves as a pointer to the first item in the list. To target the second item in Source[KP20 rate] you would use Source[KP20 rate]{1}. You can see that I set the values for the other two columns (KP20 rate and Column4) to null.

The result:

enter image description here

Here's the M code in case you want to see it:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Custom1 = Table.InsertRows(Source, Table.RowCount(Source), {[Column2 = Source[KP20 rate]{0}, KP20 rate = null, Column4 = null]})
in
    Custom1

Upvotes: 2

Related Questions