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

[hi.prashant]

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!

 

[GraH - Guido]

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)

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.prashant]

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

 

[Chihiro]

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.prashant]

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