PowerQuery - date as header

[illusionek]

Hello

PowerQuery does not accept fields formatted as date as a column name. When I used option 'Use First Row as Headers' instead of my dates I got Column3, Column4 etc

I am importing a table into PowerQuery so I can use Unpivot Columns option, so it is important to me that my dates are column headers.

As a workaround I change dates to text value before importing to PowerQuery, unpivot data, export back to Excel and then I change text value back to date value.

I was wondering if there is a smarter way of doing this?


Many thanks.

 

[Matt Allington]

Why not convert the columns to text In power query (not excel), then promote to header and then convert the columns back to what ever you need. I assume that will work.

 

[anvg]

Hi
It is a variant for example

let
    source = Table.FromRows({
    {"id",#datetime(2016,1,1,0,0,0),#datetime(2016,1,2,0,0,0),#datetime(2016,1,3,0,0,0)},
    {1,23,33,45},
    {2,31,52,26}
    }),
    headPart = Table.FirstN(source,1),
    dataPart = Table.LastN(source,Table.RowCount(source)-1),
    firstRow = Table.ToRows(headPart){0},
    dateToText = List.Transform(firstRow, each if _ is datetime then Text.From(Date.From(_)) else _),
    unpivotColNames = List.RemoveNulls(List.Transform(firstRow, each if _ is datetime then Text.From(Date.From(_)) else null)),
    newHeadPart = Table.FromRows({dateToText},Table.ColumnNames(headPart)),
    newSource = Table.Combine({newHeadPart,dataPart}),
    realHead = Table.PromoteHeaders(newSource),
    unpivoted = Table.Unpivot(realHead,unpivotColNames,"date","value"),
    return = Table.TransformColumns(unpivoted,{ {"date", each Date.From(_),type date} })
in
    return

Regards,

 

[SimonNU]

I know this is an old post but in case anyone comes across it...

To promote all headers, regardless of their data type (e.g. date), simply add the following flag:

Table.PromoteHeaders(

, [PromoteAllScalars = true])

Simply changing the table to text will not work in certain situations, e.g. ETL'ing many templates that change slightly.


Simon

 

[Carmelle]

Hi Simon,
I've spent hours trying to find a solution and came across your post. I've given it a go but it came back with an error. Maybe you can tell me if I did something wrong.

I'll run through what I did....

1. Promoted the Header
2. Selected fx.
3. What automatically appeared was = #"Promoted Headers". I replaced this with = Table.PromoteHeaders(

, [PromoteAllScalars = true])
4. This returned an error

It would be very kind of you if you assisted.

Thanks
Carmelle

I know this is an old post but in case anyone comes across it...

To promote all headers, regardless of their data type (e.g. date), simply add the following flag:

Table.PromoteHeaders(

, [PromoteAllScalars = true])

Simply changing the table to text will not work in certain situations, e.g. ETL'ing many templates that change slightly.


Simon

 

[srm057]

For Future reference. I tried to convert my data into table first by using Insert Table before using Power query. Once your data in in excel dynamic table Power QUery will automatically take date as header.