Dropdown of Columns to select date range

[DatenThielt]

Afternoon All,  I have a Table that contains about 10 fields,  and about 100,000 records  All fields are the same with exception to the parent

parentID, CreatedDate, ReceivedDate, OrderDate, HoldDate, etc

I want to be able to select one of these from a drop down, then adjust a date from and to, to display how many records reside on this date.

parentID is a Unique field and when I tried something recommended in another post (Unpivot the Columns)  This resulted in an error about the Unique field.  I'm just wondering if there is another way?

Thanks!

[freginier]

What's the error you have when you unpivot column ? 

I think the easiest way is to have two columns ParentID and Date (with all dates) 

[DatenThielt]

When I unpivot I get the error that ParentID is unique and cannot have duplicate values.   I could do the above but with 100000 records of a parent ID an date thats going to be huge,  will that not cause performance issues?

Thank you for the Response!

[V-lianl-msft]

Hi @DatenThielt ,

You can remove duplicate rows before unpivot and It is inevitable that a large dataset will affect the report performance.

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[freginier]

I unpivot 2 122 872 rows with this script...

let
    Source = Excel.Workbook(File.Contents("test.xlsx"), null, true),
    Sheet1 = Source{[Item="Feuil1",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Sheet1, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"ID", Int64.Type}, {"Col1", type date}, {"Col2", type date}, {"Col3", type date}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"ID"}, "Attribute", "Value")
in
    #"Unpivoted Columns"

Can we see your data ? 

[DatenThielt]

My data is pretty simple,   My only issue is its running from an ODBC source not spreadsheet, but considering it grabs the data fine it should be fine.

PrimaryKey	ParentID	dateNew	dateUnitReceived	dateUnitCollected	dateUnitParts	dateHeld	datePartsReceived	dateUnitShipped	dateUnitResolved
<GUID>	<INT>	<dateTime> yyyy-MM-dd hh:mm:ss	<DateTime as before>	etc	etc

[freginier]

What's happen if you apply the script above ?