Convert vertical data to horizontal and vice versa in PowerBI

Question

I have a table like this in power bi:

Channel	Month	Regular Amount	Single Amount
A	January	123.1	111.1
B	January	345.3	222.2
B	February	678.6	333.3

I need to convert the Month as column and the column Regular Amount & Single Amount as row like this:

Channel	Type	January	February
A	Regular Amount	123.1	0
A	Single Amount	111.1	0
B	Regular Amount	345.3	678.6
B	Single Amount	222.2	333.3

Can someone assist?

Answer 1

These transformation should be done in Power Query with the following steps:

1. Unpivot the Regular Amount and Single Amount columns
2. Rename columns
3. Pivot the Month column
4. Replace null values with 0

let
    // Load your data source here
    Source = ... ,
    
    // Unpivot Regular Amount and Single Amount columns
    Unpivoted = Table.UnpivotOtherColumns(Source, {"Channel", "Month"}, "Type", "Value"),
    
    // Pivot the Month column
    Pivoted = Table.Pivot(Unpivoted, List.Distinct(Unpivoted[Month]), "Month", "Value", List.Sum),
    
    // Replace nulls with 0
    ReplacedNulls = Table.ReplaceValue(Pivoted, null, 0, Replacer.ReplaceValue, List.Skip(Table.ColumnNames(Pivoted), 2)) // Skipping Channel and Type columns
in
    ReplacedNulls