Shift cells to the right in Power query?

D

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"
Sort by date Sort by votes
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
You must log in or register to reply here.

Similar threads

L
  • Solved
Power Query - filter based on cell outside table
Replies
6
Views
350
Peter_SSs
Peter_SSs
S
  • Solved
Need help on dynamic date filter in Power Query
Replies
2
Views
7K
spycein
S
A
  • Question
Power Query - Dealing with Varying Column Numbers and Cell with Varying/Multiple Values/Multiple Formats
Replies
4
Views
452
KRice
K
M
  • Question
Center across selection in a power query output
Replies
2
Views
774
MathGeek
M
M
  • Question
How to add calculated rows and calculated columns in power pivot table?
Replies
1
Views
1K
Matt Allington
M

Forum statistics

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

Share this page

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