Hello,
thanks to everyone who participated! I loved the UI orientated approaches and of course also the super cool Split technique from @borydobon. I’ll show you two relatively easy solutions: One relying on added columns plus one adapting the Split technique.
This workout combined two common real world problems. Filtering beyond standard filtering and splitting a column with changing order, inconsistent naming and special characters.
I’ll start with the added column orientated solution, you should be able to follow along even if you are not an advanced user. Changing the YEAR parameter to a number type as @AlexisOlson asked for, is definitely ok, but I’ll do without:
On the Add Column tab select Custom Column and then type Source for the column name and write a simple formula:
if Text.StartsWith([Attribute], “FC”) then “FC” else “Plan”
That’s really easy so we do this twice more.
Once with the column name Type and this formula:
if Text.Contains([Attribute], “Volume”) then “Volume” else “Sales”
And once more with the column name Year and a formula to extract the last 4 characters:
Text.End([Attribute], 4)
Now simply mark the Attribute column and press Delete to get rid of it.
Mark the Source column and filter for Text Filter
Choose Advanced and for Source equals FC and for Year equals Parameter YEAR
You will see this in the formula bar
= Table.SelectRows(#“Removed Columns”, each [Source] = “FC” and [Year] = YEAR)
Put a bracket around the condition and then copy it
= Table.SelectRows(#“Removed Columns”, each ([Source] = “FC” and [Year] = YEAR) )
Type or and paste and adapt:
= Table.SelectRows(#“Removed Columns”, each ([Source] = “FC”) and ([Year] = YEAR) or ([Source] <> “FC”) and ([Year] < Text.From(Number.From(YEAR)+5)) )
The Text.From(Number.From( allows you to work with YEAR without changing any type.
[If this filter condition building in the formula bar is difficult for you, you can also build it in an added custom column.]
Now let’s pivot the Type column. Mark it and on the Transform column select Pivot Column.
As Values Column choose Value
Finally it makes sense before loading the data to define the data type for the new columns Volume and Sales. Mark them and on the Transform column select Detect Data Type.
You’ll get whole number and type number.
That’s it. Completely doable with UI support and some tweaking.
let
Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
#"Added Source" = Table.AddColumn(Source, "Source", each if Text.StartsWith([Attribute], "FC") then "FC" else "Plan"),
#"Added Type" = Table.AddColumn(#"Added Source", "Type", each if Text.Contains([Attribute], "Volume") then "Volume" else "Sales"),
#"Added Year" = Table.AddColumn(#"Added Type", "Year", each Text.End([Attribute], 4)),
#"Removed Columns" = Table.RemoveColumns(#"Added Year",{"Attribute"}),
#"Filtered Rows" = Table.SelectRows(#"Removed Columns", each ([Source] = "FC") and ([Year] = YEAR) or ([Source] <> "FC") and ([Year] < Text.From(Number.From(YEAR)+5))),
#"Pivoted Column" = Table.Pivot(#"Filtered Rows", List.Distinct(#"Filtered Rows"[Type]), "Type", "Value", List.Sum),
#"Changed Type" = Table.TransformColumnTypes(#"Pivoted Column",{{"Volume", Int64.Type}, {"Sales", type number}})
in
#"Changed Type"
That was easy and step-by-step, but you want to have it shorter? Then try the splitting approach from @borydobon! Too difficult? Here is an alternative logic which might be easier to understand (saves one step):
let
Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
Split = Table.SplitColumn(Source, "Attribute", each
if Text.Contains(_, "Plan") then
{"Plan",Text.BeforeDelimiter(_," "),Text.End(_, 4)} else
if Text.Contains(_, "Volume") then
{"FC","Volume",Text.End(_, 4)} else
{"FC","Sales",Text.End(_, 4)}, {"Source", "Type","Year"}),
Filter = Table.SelectRows(Split, each ([Source] = "FC") and ([Year] = YEAR) or ([Source] <> "FC") and ([Year] < Text.From(Number.From(YEAR)+5)) ),
Pivot = Table.Pivot(Filter, List.Distinct(Filter[Type]), "Type", "Value", List.Sum)
in
Pivot
Try them out and make sure to check also some of the other approaches above! => Hope you learn something!
Big thanks to everyone who has blurred or hidden the details! I have taken this now out so that it’s easier to compare solutions.
