Hi Alison,

The fields in the Word doc are mapped 1-1 from the source data (columns) for each record.  So if you want to have multiple issues per Owner, you'll need a separate column for each issue related to that Owner.

regards

Phil

Is there a way to do this from the source data?  with Power Query or something else?

Hi Alison,

You can use Power Query to Pivot the data. The file attached shows the process. It's a bit complicated and you have to merge the columns for each issue.

Mynda

Thanks.  Can you explain what the query is doing please so I can understand what each step is doing.   The

The SourceData query is just your source data with the columns merged, except the Owner column. I also added an Index column.

The IssuesPivoted query references the SourceData query then groups the rows and adds a column "Count" that inserts a Table containing an index based on the Data column.

We then remove the Data column and expand the Count column to reveal the numbered rows which are then pivoted.

The best way to understand the queries is to inspect each step, clicking on the gear icons where available, to get a closer look at the workings etc. This coupled with completing the Power Query course will help you follow the steps.

Mynda

I've watched the video but still unsure what it is doing.

What is the Data.Index column representing?   How does it work out the numbered column

Hi Alison,

What video are you referring to having watched?

What query and step is the 'Data.Index' column in? I can't see it in the file I attached.

Mynda

The power query videos

The file you sent - I added a step to expand the data column  to try and work out what was happening in the background by I’m still none the wiser. 

Hi Alison,

You've only watched 21 of the 71 videos from the course, so I'm not sure which video you're referring to.

The Data column is the result of grouping the rows. Adding a step that wasn't in the file won't help you understand what is going on, it will only mess up the results. You need to follow the steps that are in the file and click on the gear icons to see the settings. A lot can be learnt from this.

The 'Added Custom' step references the 'Data' column which is the result of the grouping, but then we remove the Data column because it's not required, as you can see in the 'Applied Steps'.

The Added Custom Step M code is:

= Table.AddColumn(#"Grouped Rows", "Count", each Table.AddIndexColumn([Data], "Numbered", 1, 1))

It adds an Index column starting at 1 and counting up by 1 based on the grouped data in the Data column.

It's an advanced technique that allows you to number grouped data with the numbering resetting at each group.

Mynda

Thanks.  The last paragraph answers the question but I couldn’t see how it was getting to that.  I thought it may have been looking at something from the hidden data columns hence adding a step to immediately delete it once I’d looked and not messing up the results as you suggest.  I’d already looked at the gears before I asked the question - I then went and watched the videos I thought relevant to the question as you suggested but still I was none the wiser.  Which particular video explains this advanced technique?

re the videos.   My time is limited so I’m trying to find videos that show me things I don’t already know how to do or better ways to get to the results I’m calculating - having been self taught

 I looked at the video for adding an index column - the mechanics very simple.   The why I’d want to do that not really explained.

thanks for the answer - we got there in the end

Grouping is covered in session 4.16. Index is covered in 4.13, but there are examples of Index being used for various solutions scattered throughout the course as it's a versatile tool. That's why it's important that you complete the course as it will give you a more rounded understanding of the tools and how you might use them.

You have 12 months to complete the courses, there is no major rush. The 6 weeks of support is for you to keep momentum with your learning and not get stuck at a particular tutorial, not so you can find solutions to every work issue you have right now without first completing the courses. There is plenty of time to get help with specific work questions once you have completed the courses. In fact you're more likely to be able to solve these questions yourself if you have the course experience to fall back on.