Data Cleansing

[Anonymous]

Hi Everyone,
Im struggling with this query downloaded from SAP where i need to put the information from row 2 until row 4 in columns  using power query. In the firts image "SAP" you will see how the information is download and in the second what i would like to do.

Thanks !SAP QUERYwhat i need

[mahoneypat]

Here is one way to do it in the query editor.  Note - I had to use OCR to get sample data from your image, so it has some gaps, but you can see the approach to use.  To see it, create a new blank query, go to Advanced Editor, and paste this code over the text that is there:

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("vZRdb9owFIb/isXVJlHLHzm2czVBKFtVvkrDpg31IgNfIKV4Ssy0/vs5xMGh04BqaFLsROd1zuP3yMfLZeduoG2W57pEiSk2u2dTorVGs8Ksd6vVxmw73c6p56m7rN7AInmDZtk2e9bFZuXe535K9NYW5l35voVzGiOMnPu1V2wyNNyUqyyv0OeWz9yOzHqPmpgL7Dya3KBpsdbb/b4wSkaPaGYK5BNdkGJsSltkBRpk1pQXrK9pSWnTlx/aBceZdawsR6n+ZQ36XuifGi3GfSclOXZzXTCLBrqsqvahGvVUzYnZlp+zfKf3KyfaPtgXH66/UmO/lNYtQTd7vlTABKdoPnaikDJmQgLqj3rJPUrSBA3Q/UenfEuH1RYAmOLSybeTARrdzdKpC5LWYBQzTrEA7gMCU0675JVWkwVx39ciU4k5Z5hT8AHAhEBNbmk1mStHb5MV9eTpPP10BOYEKPHc+fRr7zVWVGkdihwMAyEeGzRfaoeKjgxH/2AYsFQSsxaZRQ05aDU5kpKoq5EjrAipfB5KraAhB817dhcEvSKZRgzLFhniQG60QL5etTlW1WiRI9GQgxZaSlx6sJ0Yc5QsEnfbLPoovf2TDARanjmOSSA3mj/YTEVwRGYt8rD7ZtNMidbhjjBAQDeaP2IkjuOrdXOVnWBB4GBacWiRa+0v3XyCfK6dWZUXcxaKLQ5NFbTm/gKhLuQKSWXs9nnK81vYkQJxvWq7u9FVlLFQbZBwIDeaP9ycwX8nP/0G", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t, Column5 = _t, Column6 = _t, Column7 = _t, Column8 = _t, Column9 = _t, Column10 = _t, Column11 = _t]),
#"Added Custom" = Table.AddColumn(Source, "New1", each Source{1}[Column2]),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "New2", each Source{2}[Column2]),
#"Added Custom2" = Table.AddColumn(#"Added Custom1", "New3", each Source{3}[Column2]),
#"Removed Top Rows" = Table.Skip(#"Added Custom2",7),
#"Promoted Headers" = Table.PromoteHeaders(#"Removed Top Rows", [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Orden CstType", type text}, {"Material Texto breve UMB", type text}, {"Cl.", type text}, {"Product Desc", type text}, {"?", Int64.Type}, {"??", Int64.Type}, {"???", Int64.Type}, {"ConsValue", type text}, {"PrNetQty", Int64.Type}, {"ConsQty", type text}, {"TotWstVal -", type text}, {"5247- Panamericana", type text}, {"2020", Int64.Type}, {"5", Int64.Type}}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"5247- Panamericana", "Plant"}, {"2020", "Year"}, {"5", "Period"}}),
#"Reordered Columns" = Table.ReorderColumns(#"Renamed Columns",{"Plant", "Year", "Period", "Orden CstType", "Material Texto breve UMB", "Cl.", "Product Desc", "?", "??", "???", "ConsValue", "PrNetQty", "ConsQty", "TotWstVal -"})
in
#"Reordered Columns"

Another approach to consider (assuming you are combining many of these reports together) is to name the files in a way that is easy to extract the data you need.  Plant_Year_Period.xlsx, for example.  That way you can just remove the top 7 rows with a function for each worksheet and then split the file name column by _ delimiters and rename your columns.  Of course, you can do the above in your function too and it should work well.

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat