• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Power Query - How to Merge rows & Move data in columns

hi.prashant

New Member
I get the attached sales report from my ERP system. I have to do some transformations in the downloaded report every month before analyzing it. I would like to automate it through Power Query. Please refer attached file.
- use data in row 3&4 as header
- data appears in blank column M in few rows , which is essentially shifted by one cell to the left from column L, reinstate it (main problem)
- remove blank column M
- remove sum row at the bottom
Thanks in advance!
 

Attachments

  • ChandooForum.xlsx
    80.5 KB · Views: 6
Hi,

First of all I have a question on the export from ERP. By any chance is that export done in a CSV format? My experience is that such export have lesser (to none) issues with columns being shifted. And during import via PQ there might be a way to easily avoid the issue.
I'm guessing the lay-out is like this because the columns N:W are aggregations and the columns A:L are dimensions.

A way to deal with the rest (not the shifting of data. I also saw some red lines and data shifted "up"?):
  1. Load data from table/range // or from csv, but then there are some additional steps
  2. Select the columns 14 until 23
  3. Fill down (the headers are copied down on row 4)
  4. Remove top rows count 3
  5. Remove bottom row count 1
  6. Promote headers
  7. Delete Column 13
  8. Save and load to Excel (as Table or even PivotTable)

Code:
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"FillDown_HeadersCol14-23" = Table.FillDown(Source,{"Column14", "Column15", "Column16", "Column17", "Column18", "Column19", "Column20", "Column21", "Column22", "Column23"}),
    RemoveTopRows_4 = Table.Skip(#"FillDown_HeadersCol14-23",4),
    PromoteHeaders = Table.PromoteHeaders(RemoveTopRows_4, [PromoteAllScalars=true]),
    RemoveBottomRow_1 = Table.RemoveLastN(PromoteHeaders,1),
    RemoveCol_13 = Table.RemoveColumns(RemoveBottomRow_1,{"Column13"})
in
    RemoveCol_13
 
Hi GraH,
Thanks for the reply,
because of some strange reason data export is not done in a CSV format,
rather i m copying the data from ERP and pasting it in excel.
till the issue is resolved my main problem remains shifting of data,
the red lines is the data shifted by one cell to the right from column L
 
Try pasting the data into text editor (Notepad++ etc). And study what special character (non-printing) are there.

Often case different system does not always use the same line ending. Ex: Windows based system use CrLf, where as Unix system use Lf. etc.

As well, you may find some other edge case where odd non-printing characters are inserted. Especially in web pages / web based reports.

Best way to handle this is by pasting data into text editor, save as txt/csv. Then import data using PowerQuery. This way you control the ETL process.
 
Hi Chihiro,
Thanks for the reply,
Attached is a work around of my problem, i feel its inefficient, just want you to check
-i have made two duplicate queries one with no column data shifted (table1) other with column data shifted (table2)
-delete the columns with null data in both tables
-transpose and re-transpose both the tables to get same column number header (to avoid problem during appending queries)
-append and load
 

Attachments

  • ChandooForum.xlsx
    113.1 KB · Views: 1
Back
Top