none
Combine Values into one column using Pivot RRS feed

  • Question

  • I am trying to get the values from the table to look like the values on the right screen shot. I used the Pivot bit got an error because I had more than one value for ABC January. If more than one value then separate with comma. 

    Friday, November 22, 2019 9:05 PM

All replies

  • Hello

    use Power Query to read your data.

    Then use function Table.Pivot and Text.Combine to get desired result. Here the M-Code


    let
        Source= Excel.CurrentWorkbook(){[Name="tblData"]}[Content],
        PivotColumn = Table.Pivot(
            Source, 
            List.Distinct(
                Source[Month]),
            "Month",
            "Sector",
            each Text.Combine(_, ", ")
            )
    in
        PivotColumn
    Hope it helps


    Query it


    • Proposed as answer by jimmy80211 Saturday, November 23, 2019 8:35 AM
    • Edited by jimmy80211 Saturday, November 23, 2019 8:36 AM
    Saturday, November 23, 2019 8:35 AM
  • Thanks, that did it. I was missing a section.
    Wednesday, November 27, 2019 3:36 PM
  • So please mark it as answer

    Query it

    Wednesday, November 27, 2019 8:17 PM