SOLVED

Pivot Table with multiple date columns (Power Query?)

%3CLINGO-SUB%20id%3D%22lingo-sub-2008531%22%20slang%3D%22en-US%22%3EPivot%20Table%20with%20multiple%20date%20columns%20(Power%20Query%3F)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2008531%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Everyone%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20hope%20you%20are%20all%20well.%20It's%20my%20first%20post%20here%2C%20so%20apologies%20in%20case%20I%20have%20missed%20anything.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20attached%20my%20xls%20Example%20below.%20I%20have%20a%20number%20of%20IDs%20(departments)%20on%20the%20rows%2C%20and%20order%20dates%20and%20quantities%20on%20the%20columns.%20I%20know%20this%20setup%20isn't%20ideal%20for%20pivot%20tables%2C%20but%20this%20is%20the%20data%20source%20I%20am%20forced%20to%20use.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'd%20like%20to%20understand%20how%20to%20have%20an%20easy%20way%20of%20reading%20this%20data%20source%20with%20power%20pivot%20or%20power%20query.%20The%20ultimate%20goal%20is%20to%20have%20a%20pivot%20table%20that%20clearly%20shows%20the%20order%20quantities%2C%20split%20by%20date%2C%20for%20each%20department%20(ID).%20I%20have%20seen%20a%20similar%20post%20(here%3A%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fexcel%2Fpivot-table-with-multiple-date-columns%2Fm-p%2F1634183%22%20target%3D%22_blank%22%3Ehttps%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fexcel%2Fpivot-table-with-multiple-date-columns%2Fm-p%2F1634183%3C%2FA%3E)%20where%20power%20query%20was%20used%20to%20resolve%20this%20issue%2C%20but%20in%20my%20case%20it%20doesn't%20seem%20to%20work%20when%20I%20unpivot%20columns...%20I%20still%20seem%20to%20have%20the%20same%20readability%20issue...%20Any%20chance%20you%20could%20kindly%20help%20me%20resolve%20this%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20this%20example%2C%20for%20instance%2C%20I'd%20like%20to%20clearly%20see%20in%20a%20pivot%20table%20that%20Department%20C%20has%20ordered%3A%3C%2FP%3E%3CP%3E-%202%20units%20on%2023%2F12%2F2020%3B%3C%2FP%3E%3CP%3E-%202%20units%20on%2024%2F12%2F2020%3B%3C%2FP%3E%3CP%3E-%202%20units%20on%2029%2F12%2F2020%3B%3C%2FP%3E%3CP%3E-%201%20unit%20on%2030%2F12%2F2020%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20for%20any%20help%20or%20feedback.%20Much%20appreciated!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBest%20regards%2C%3C%2FP%3E%3CP%3ELuca%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2008531%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2008548%22%20slang%3D%22en-US%22%3ERe%3A%20Pivot%20Table%20with%20multiple%20date%20columns%20(Power%20Query%3F)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2008548%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F403176%22%20target%3D%22_blank%22%3E%40Riny_van_Eekelen%3C%2FA%3E%26nbsp%3Bperhaps%20can%20help%3F%20I%20saw%20you%20helped%20on%20the%20other%20post%20I%20mentioned%20above.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20so%20much%2C%3C%2FP%3E%3CP%3ELuca%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2008595%22%20slang%3D%22en-US%22%3ERe%3A%20Pivot%20Table%20with%20multiple%20date%20columns%20(Power%20Query%3F)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2008595%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F909127%22%20target%3D%22_blank%22%3E%40Luca_Morrone91%3C%2FA%3E%26nbsp%3BSee%20attached!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2011447%22%20slang%3D%22en-US%22%3ERe%3A%20Pivot%20Table%20with%20multiple%20date%20columns%20(Power%20Query%3F)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2011447%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F403176%22%20target%3D%22_blank%22%3E%40Riny_van_Eekelen%3C%2FA%3E%26nbsp%3B%20Thanks%20so%20much%20Riny!%20It%20works%20great%20%3CLI-EMOJI%20id%3D%22lia_slightly-smiling-face%22%20title%3D%22%3Aslightly_smiling_face%3A%22%3E%3C%2FLI-EMOJI%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

Hi Everyone,

 

I hope you are all well. It's my first post here, so apologies in case I have missed anything.

 

I have attached my xls Example below. I have a number of IDs (departments) on the rows, and order dates and quantities on the columns. I know this setup isn't ideal for pivot tables, but this is the data source I am forced to use.

 

I'd like to understand how to have an easy way of reading this data source with power pivot or power query. The ultimate goal is to have a pivot table that clearly shows the order quantities, split by date, for each department (ID). I have seen a similar post (here: https://techcommunity.microsoft.com/t5/excel/pivot-table-with-multiple-date-columns/m-p/1634183) where power query was used to resolve this issue, but in my case it doesn't seem to work when I unpivot columns... I still seem to have the same readability issue... Any chance you could kindly help me resolve this?

 

In this example, for instance, I'd like to clearly see in a pivot table that Department C has ordered:

- 2 units on 23/12/2020;

- 2 units on 24/12/2020;

- 2 units on 29/12/2020;

- 1 unit on 30/12/2020;

 

Thanks for any help or feedback. Much appreciated!

 

Best regards,

Luca

4 Replies

@Riny_van_Eekelen perhaps can help? I saw you helped on the other post I mentioned above.

 

Thanks so much,

Luca

Best Response confirmed by Luca_Morrone91 (Occasional Contributor)
Solution

@Luca_Morrone91 See attached!

 

@Riny_van_Eekelen  Thanks so much Riny! It works great

@Luca_Morrone91 Great! Glad I could help.