Pivoting a single row and then filling down as a sample file

[Anonymous]

Hi All,

I have a bunch of CSVs in a folder from here: www.nutritionvalue.org

You can obtain one via https://www.nutritionvalue.org/Sardines%2C_dried_26139170_nutritional_value.html?size=1+kg+%3D+1000+... and clicking "download csv".

As a sample file, I get this:

Prior to combining all the files, I want to extract and pivot into columns the data items in row 5, column 1, and row 5, column 2, as two separate columns.

From there, I'd probably delete the top six rows, which completes the sample file. This would then continue as the sample file for all the other files.

Does that make sense? What is the best way to process this during the sample file creation, or is there a better way?

Thanks in advance, and kind regards

[Anonymous · Accepted solution]

HI @Anonymous,

You can refer to the following M query codes to modify the table structure. (include functions: remove blank rows, remove top N rows, remove bottom N rows, remove columns, transpose table, use first row as header)

let
    Source = Csv.Document(File.Contents("C:\Users\xxxxxx\xxxxxxx\sardines_dried.csv"),[Delimiter=",", Columns=4, Encoding=65001, QuoteStyle=QuoteStyle.None]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}}),
    #"Removed Blank Rows" = Table.SelectRows(#"Changed Type", each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {"", null}))),
    #"Removed Top Rows" = Table.Skip(#"Removed Blank Rows",5),
    #"Removed Bottom Rows" = Table.RemoveLastN(#"Removed Top Rows",3),
    #"Removed Columns" = Table.RemoveColumns(#"Removed Bottom Rows",{"Column3", "Column4"}),
    #"Transposed Table" = Table.Transpose(#"Removed Columns"),
    #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table", [PromoteAllScalars=true])
in
    #"Promoted Headers"

Regards,

Xiaoxin Sheng