say it with moxy

A practical use case for New Rows feature in Tableau Prep

Have you ever received a highly-formatted spreadsheet from your Finance department to be used as a data source for company's most important reporting needs? Of course you have! 80% of the time, this happens every time.


Not only does the spreadsheet have a bunch of unnecessary elements for a data source, it's almost never at the level of granularity your end users want (read "need"). Data clean-up tools like Power Query Editor, Alteryx and Tableau Prep can be a life saver in these situations.


Using a fake scenario where I'm responsible for transforming a highly-formatted spreadsheet with revenue targets at the month level into a (re)usable data source with daily revenue targets, I've recorded this 20-minute video tutorial showing you how to do exactly that with the New Rows feature in Tableau Prep. Here are the steps at a high-level:

  1. Basic data clean-up

  2. Create calculated fields

  3. Start of Month: DATETRUNC('month', [Date])

  4. End of Month: DATEADD('second', -1, DATEADD('month', 1, [Start of Month]))

  5. Days/Month: DATEDIFF('day', [Start of Month], [End of Month])+1

  6. Daily Plan: [Monthly Plan]/[Days/Month]

  7. Filter to single Channel/dimension

  8. Copy node and filter to a different Channel/dimension (Repeat for each value in your dimension)

  9. Add New Rows node to each filtered node

  10. Set "Value Ranges from two fields": Start of Months & End of Month

  11. Set to "Copy from previous row"

  12. Union all New Rows nodes back together

  13. Create output node as needed.

Download the packaged workflow if you have Tableau Prep and you'd like to give it a try yourself.


Don't have Tableau Prep yet? Download a trial here.


Hope you found this helpful!



0 comments

Recent Posts

See All

Enabling a data culture is about making small, incremental changes, and being consistent and persistent about it, over immediate gains.

Moxy Analytics logo