title: Normalizing a csv field in power query (M)
body: <h2>Background</h2>
  
  <p>I am building a simple dashboard in the Power BI plugins for Excel (Power Query, Power Pivot &amp; Power View) to get some experience with Power BI. The dashboard is for presenting simple time reports made by a consultant (i.e. myself). The format i want to use for inputting data is in a Excel table as follows:</p>
  
  <pre><code>InputData:
  Date       | Timecode  | Duration[hrs] | Tags
  -----------|-----------|---------------|----------------------
  2016-02-01 | CustomerA | 1.2           | Support;ProductA
  2016-02-01 | CustomerB | 0.3           | Support;ProductB
  2016-02-02 | Internal  | 4.2           | Development;ProductA
  </code></pre>
  
  <p>The <code>Date</code> field is simple. The date that the time report is for. The <code>Timecode</code> is the "name" of the hours reported. In common software this is usually a Project code or similar, but i want to keep it on a customer basis. The <code>Duration</code> is a float representing the number of hours spent for that Timecode that day. The <code>Tags</code> column is the interesting part: to simplify input is want it to be a (semicolon) delimited string, but that wont do when creating a data model for Power view.</p>
  
  <p>What I am trying to make is a separate table with the all the tags, and a link table to connect the tags to the corresponding rows from the time report. In the Power view report, I want to be able to filter my time reports on the tags, such as analyzing the time spent on  <code>ProductA</code> or <code>Support</code>.</p>
  
  <h2>Question</h2>
  
  <p>How do you take a non-normalized field such as <code>Tags</code> above and replace it with a dimension table and a link-table, using Power BI plugins for Excel? How do I end up with the following Three tables:</p>
  
  <pre><code>TimeReport:
  Date       | Timecode  | Duration[hrs] | TimeReportID
  -----------|-----------|---------------|----------------------
  2016-02-01 | CustomerA | 1.2           | 1
  2016-02-01 | CustomerB | 0.3           | 2
  2016-02-02 | Internal  | 4.2           | 3
  
  LinkTable:
  TimeReportID | TagID  
  -------------|--------
  1            | 1
  1            | 2
  2            | 1
  2            | 3
  3            | 4
  3            | 2
  
  TagsTable:
  TagID  | TagName  
  -------|----------
  1      | Support 
  2      | ProductA
  3      | ProductB
  4      | Development
  </code></pre>
  
  <h2>Attempt</h2>
  
  <p>By picking out only the Tags-column and then splitting, pivoting and removing duplicates (inspired by <a href="http://blog.crossjoin.co.uk/2013/08/16/some-power-querym-examples/" rel="nofollow">this link</a> i have managed to create the list of all tags as in:</p>
  
  <pre><code>Tags:
  TagName
  ----------
  Support
  ProductA
  ProductB
  Development
  </code></pre>
  
  <p>But I cant manage to understand how to link the tables to eachother. Please aid me in this.</p>