Working with Separate Columns for Date

[Elliott]

Hello All,

Back to basics for me on this one! 

Using BI for a while now and have created plenty of reports, most of which I have been using SQL data, which appears to be very user friendly with the format when importing into BI.

However, I have been working with Excel sheets most recently and have come across the same issues with the majority of them.. I am sure there is a very simple solution to this as this is a very common format for spreadsheets, but just can't get my head round it!

Above is the prime example of the type of data I am trying to bring into BI through Excel.

As the data is input into SQL line-by-line there is always an individual date stamp next to each record, compared to the Excel sheet which will only have 1 date, which is set at the column header, to cover any number of records input into the table.

This then makes it awkward when bringing the data into BI, for instance when attempting to import a slicer so that I can select my specified days. With the SQL data this would have been as straight forward as dropping the date column into the slicer, however, how can I achieve the same result with the format in which the example Excel sheet is set out? (multiple dates in their own columns)

Ultimately, what I am looking to achieve is a simple graph to show the 'fruit' as the X-axis and then filter by date (in this case, filter by a specified date column)

(As example below)

Thank you in advance!

[Greg_Deckler · Accepted solution]

With your example, try unpivoting the date columns, like this:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WciwoyElV0lEyNAASxgZKsTrRSv5FiXnpcEEjiGBAamIRhAcWBwk5JeYBIbJgLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"(blank)" = _t, #"1/1/2016" = _t, #"2/1/2016" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"(blank)", type text}, {"1/1/2016", Int64.Type}, {"2/1/2016", Int64.Type}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"(blank)", "Fruit"}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Renamed Columns", {"Fruit"}, "Attribute", "Value")
in
    #"Unpivoted Columns"

You should end up with a table like:

 

[Greg_Deckler · Accepted solution]

With your example, try unpivoting the date columns, like this:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WciwoyElV0lEyNAASxgZKsTrRSv5FiXnpcEEjiGBAamIRhAcWBwk5JeYBIbJgLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"(blank)" = _t, #"1/1/2016" = _t, #"2/1/2016" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"(blank)", type text}, {"1/1/2016", Int64.Type}, {"2/1/2016", Int64.Type}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"(blank)", "Fruit"}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Renamed Columns", {"Fruit"}, "Attribute", "Value")
in
    #"Unpivoted Columns"

You should end up with a table like:

 

[Elliott]

Perfect,

Easy when you know how! 

Thanks alot @Greg_Deckler

[Elliott]

The example you have shown just there looks spot on.

I'll give it a go and see how I get on

Thanks for the quick response!

[Greg_Deckler · Accepted solution]

You don't have to write the "M" code, just select your date columns (all of them) and right click and choose "Unpivot Columns"