Shift cells to the right in Power query?

danhendo888

Board Regular
Joined
Jul 15, 2019
Messages
154
Office Version
  1. 365
Platform
  1. Windows
Is it possible to move the highlighted columns below to the right in power query?

Start:
InvoiceColumn3DateRefDueAmountTotal
S0025562815-Apr-24Text20-May-24404.70404.70
S0025562915-Apr-24Text20-May-24279.70279.70
S0025487828-Mar-24PAYMENT REF: 0044316120-Apr-24479.64479.6425,387.81
W0000145028-Mar-24PAYMENT REF: 0044324620-Apr-2461.8061.8025,449.61
W0000148415-Apr-24Other20-May-2410.80
S00252765/A16-Apr-24Other20-May-24879.03


I've opened the original file from PDF, and in the second column, if there are values then I'd like to push the cells to the right.
The order in which the rows appear in either table is not important. The cells are only highlighted for explanation purposes, no formatting is required.

Output:
InvoiceColumn3DateRefDueAmountTotal
S0025562815-Apr-24Text20-May-24404.70404.70
S0025562915-Apr-24Text20-May-24279.70279.70
S0025487828-Mar-24PAYMENT REF: 0044316120-Apr-24479.64479.6425,387.81
W0000145028-Mar-24PAYMENT REF: 0044324620-Apr-2461.8061.8025,449.61
W0000148415-Apr-24Other20-May-2410.80
S00252765/A16-Apr-24Other20-May-24879.03
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
Hi @danhendo888,

try

Power Query:
= Table.FromRows(List.Transform(Table.ToRows(Source), (x) => if x{1} = null then List.InsertRange(x,List.Count(x), {null}) else List.InsertRange(x,1, {null})),
Table.ColumnNames(Source) & {"Additionnal Column"})

Regards,
 
Upvote 1
Solution

Forum statistics

Threads
1,216,500
Messages
6,131,016
Members
449,615
Latest member
Nic0la

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top