Columns to rows

[Anonymous]

Hi,

I have read many articles and tutorials. Also searched on this forum to find a solution for my problem. Unfortunately, couldnt find it.

Basically, my data source looks like below 

The outcome should look like this:

The way I managed to produce above was creating 2 queries referencing the source (query 1 - filtered 2019 and query 2 - filtered 2020) then created additional query to merge the referecing queries. Here is the the code: 

let
Source = #"Ref 2020",
#"Removed Columns" = Table.RemoveColumns(Source,{"Year", "Impression", "cost "}),
#"Merged Queries" = Table.NestedJoin(#"Removed Columns", {"Campaign"}, #"Ref 2019", {"Campaign"}, "Ref 2019", JoinKind.LeftOuter),
#"Expanded Ref 2019" = Table.ExpandTableColumn(#"Merged Queries", "Ref 2019", {"Impression", "cost "}, {"Ref 2019.Impression", "Ref 2019.cost "}),
#"Merged Queries1" = Table.NestedJoin(#"Expanded Ref 2019", {"Campaign"}, #"Ref 2020", {"Campaign"}, "Ref 2020", JoinKind.LeftOuter),
#"Expanded Ref 2020" = Table.ExpandTableColumn(#"Merged Queries1", "Ref 2020", {"Impression", "cost "}, {"Ref 2020.Impression", "Ref 2020.cost "})
in
#"Expanded Ref 2020"

The problem with above method is uncessary creating multiple queries which is not ideal in terms of refreshing times. I tried to upload the file but didnt support it.

I really hope you can help me with this! Many thanks.

[amitchandak · Accepted solution]

@Anonymous , You can create a matrix visual and display it with year on the column, campaign on row, impression, and cost on values 

If you need a table.

1. Unpivot Cost and impression

2. Concat year and category column in new measure name and remove the two {year, measure name}

header = [Year] & "." &[Measure name]

3 then pivot header and value 

[Anonymous]

Thank you so much for your help. I tried it and it works. You are a legend.