Forum Discussion
Columns to rows
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.
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
2 Replies
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