Forum Discussion
ADF pivot vs fabric dataflow pivot
I created the helper row by using Enter Data. Then I copied the generated M code into the main query manually.
But you can do it easier:
You can create it as a separate query by using Enter Data, and then appending that query to your main query by using the menu option for Append queries.
In the remove duplicates step, you need to specify which columns shall be considered when comparing the rows.
If the difference between the rows exists in some column which is not specified in the removed duplicates step, then it will not detect that there is a difference between those rows.
So you will need to specify which columns to check.
Currently, it is only checking the columns which are mentioned here:
{"venture_number", "book", "movement_name", "strategy", "name", "actual_date", "estimated_date", "gross", "net", "actual", "percentage"}
for the unpivot, which columns do I need to select first? thanks
I'm guessing all the date columns
in your example, you do not have title date in unpivot result whereas you have title date in group by result. is this wrong?
I think the reason is because the title date is always null.
If you want to add some columns, even if they only have null values, you could consider appending a dummy row just to create those columns:
let HelperRow = let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i44FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [venture_number = _t, book = _t, movement_name = _t, strategy = _t, gross = _t, net = _t, actual = _t, percentage = _t, #"despatch date actual_date" = _t, #"despatch date estimated_date" = _t, #"arrival date estimated_date" = _t, #"arrival date actual_date" = _t, #"title date actual_date" = _t, #"title date estimated_date" = _t]), #"Changed column type" = Table.TransformColumnTypes(Source, {{"venture_number", Int64.Type}, {"gross", type number}, {"net", type number}, {"actual", type number}, {"percentage", Int64.Type}, {"despatch date actual_date", type date}, {"despatch date estimated_date", type date}, {"arrival date estimated_date", type date}, {"arrival date actual_date", type date}, {"title date actual_date", type date}, {"title date estimated_date", type date}}) in #"Changed column type", Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyNlHSUUrKz882BNK5+WWpual5JSB2cUlRYklqeiWInZJaXJBYkpyhkAIUAvINjfQMLPWMDIyMwRx9A0t9KAeCDA0M9AyMQDoNlGJ1aGYNCNHEeJD7DU0N4bbQ1BejgUWsLxAhNUitKcksyUmF2YGMyIxjnOaRGJlYzKEo1ka2P6mdPsg3L7GoKLMsMQfJRCMLoFF61ChfiDKbxPghYCZFcTUaFqS4l5ZpDt3sWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [venture_number = _t, book = _t, movement_name = _t, strategy = _t, name = _t, actual_date = _t, estimated_date = _t, gross = _t, net = _t, actual = _t, percentage = _t]), #"Changed column type" = Table.TransformColumnTypes(Source, {{"venture_number", Int64.Type}, {"book", type text}, {"movement_name", type text}, {"strategy", type text}, {"name", type text}, {"gross", type number}, {"net", type number}, {"actual", type number}, {"percentage", Int64.Type}}), #"Changed column type with locale 1" = Table.TransformColumnTypes(#"Changed column type", {{"actual_date", type date}, {"estimated_date", type date}}, "nb-NO"), #"Removed duplicates" = Table.Distinct(#"Changed column type with locale 1", {"venture_number", "book", "movement_name", "strategy", "name", "actual_date", "estimated_date", "gross", "net", "actual", "percentage"}), #"Grouped rows" = Table.Group(#"Removed duplicates", {"venture_number", "book", "movement_name", "strategy", "name"}, {{"actual_date", each List.Max([actual_date]), type nullable date}, {"estimated_date", each List.Max([estimated_date]), type nullable text}, {"gross", each List.Max([gross]), type nullable number}, {"net", each List.Max([net]), type nullable number}, {"actual", each List.Max([net]), type nullable number}, {"percentage", each List.Max([percentage]), type nullable Int64.Type}}), #"Unpivoted only selected columns" = Table.Unpivot(#"Grouped rows", {"actual_date", "estimated_date"}, "Attribute", "Value"), #"Changed column type with locale" = Table.TransformColumnTypes(#"Unpivoted only selected columns", {{"Value", type date}}, "nb-NO"), #"Merged columns" = Table.CombineColumns(#"Changed column type with locale", {"name", "Attribute"}, Combiner.CombineTextByDelimiter(" ", QuoteStyle.None), "Merged"), #"Pivoted column" = Table.Pivot(Table.TransformColumnTypes(#"Merged columns", {{"Merged", type text}}), List.Distinct(Table.TransformColumnTypes(#"Merged columns", {{"Merged", type text}})[Merged]), "Merged", "Value"), #"Appended query" = Table.Combine({#"Pivoted column", HelperRow}) in #"Appended query"