Appended CSVs, searching rows to promote headers

[OKgo]

Target format

The raw data is many CSV files appended together. Some CSVs contain more columns than others:

My plan is to unpivot this data to the target format. Where I am struggling is trying to isoloate / search the correct row to promote to headers. Thank you in advance

[v-juanli-msft]

Hi @OKgo

Please look at my test

Above it is an original table called “Sheet2”, it has five columns needed to be unpivot and two columns don’t need to unpivot.

1.Then I make a copy of “Sheet2”, thus a new table called “Sheet2copy”.

In this table, you could click on the “setting” icon on the “APPLIED STEPS” pane to see the detailed operation I made.

First, I select all columns and click “Transform->Replace Values”, then replace “null” with “0”,

Second, I add a conditional column(called “Custom”) to define which row contains date type value and which row contains number type value.

Third, I select the five columns(column1-5) and click on “unpivot the selected columns”.

2. create a blank query called “Query2”, and write code In Advanced editor

let

    Source1 = Sheet2copy,

    Source2 = Table.SelectRows(Source1,each[Custom]="number")

in

Source2

3.make a copy of Sheet2copy, the new table is called “Sheet2copy (2)”

then click on “Keep Rows->Keep Range of Rows” to keep the rows which “Custom” is “date”,

Then merge queries by column “Attribute” between “Sheet2copy (2)” and “Query2”,

Best regards

maggie

[OKgo]

Hi Maggie

Thank you so much. I should have explained better. A large number and unknown number of CSV files are contained in a folder.  Using the expanded data function they are combined. They can not be merged/appended as the headers do not match. Here is an example of two CSV files appened together.

Columns A to E have consisent headers in the data.

Columns F onwards are alwasy the first day of the month or blank.

I do not konw how to create  a custom colum to return Row 1 = Date, Row 2 = Number

Many thanks

[abbasabdulla]

Hi,

Check the linked file https://www.dropbox.com/s/6pdix2vg1dl27l1/Append%20CSV%20PQ.xlsx?dl=0

will this solution works for you?

[Ashish_Mathur]

Hi,

What if you do not promote headers but just use "Unpivot other columns"?  Does that work?

[OKgo]

Unfortuneatly not. Then I have one column of values and one column of mixed values "MMM-YY" and "ColumnX", "ColumnY" etc...

[Ashish_Mathur]

I guess this is happening because the headers on each CSV file are not and can not be the same because they are dates.  I think i can solve this problem, if you can:

1. Have one header row per file with only the day portion of the date as a header.  So if the header for column B's data is 17/7/2018, change it to 17 only.  This way there will only be 31 headers (columns) for all CSV files put together.
2. Each CSV file name should carry the month name and Year

Can you carry out the 2 changes suggested above?

[OKgo]

1.Have one header row per file with only the day portion of the date as a header. So if the header for column B's data is 17/7/2018, change it to 17 only. This way there will only be 31 headers (columns) for all CSV files put together.
OKgo Response:  The columns are months (MMM-YY). Thankfully not days. Having upto 10 years (120 months) of columns is common. There are also 5 or so text columns with other peices of information that do not need to be unpivoted. With your suggusted approach this would make the data set very difficult to analyse and break all my exisiting measures.

2. Each CSV file name should carry the month name and Year

OKgo Response: Typically 6 x CSVs with between 100 months and 120 months of data. I don't quite understand the value of doing this.

[Ashish_Mathur]

Hi,

I cannot think of any other technique.  Since the header in every file is different, the append comand is not working.

[OKgo]

Thanks Ashish, I try a dummy CSV file tomorrow with just the headers and filename that will appear first in order to promote them. Not an elgant work around but hopefully it works.