Search code examples
excelpowerbipivotpowerquery

Power Query pivot

I'm having trouble with power query trying to convert this:

enter image description here

Into this:

enter image description here

Solution

  • Click select 2nd column. Transform ... Pivot Column ... and use Recuento as Values Column

    let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(Source, {{"Actual__c", type text}}, "en-US"), List.Distinct(Table.TransformColumnTypes(Source, {{"Actual__c", type text}}, "en-US")[Actual__c]), "Actual__c", "Recuento", List.Sum)
in #"Pivoted Column"

    enter image description here

    Fancy version that pre-appends the column name

    let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
Change = Table.TransformColumns(Source,{{Table.ColumnNames(Source){1}, each Table.ColumnNames(Source){1}&"_"&Text.From(_)}}),
#"Pivoted Column" = Table.Pivot(Change, List.Distinct(Change[Actual__c]), "Actual__c", "Recuento", List.Sum)
in  #"Pivoted Column"