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

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

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

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

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

3. Filter to single Channel/dimension

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

5. Add New Rows node to each filtered node

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

   2. Set to "Copy from previous row"

6. Union all New Rows nodes back together

7. 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!