Forum Discussion
ADF pivot vs fabric dataflow pivot
there is a powerful pivot activity in ADF dataflow.
the pivot in fabric dataflow gen2 does not seem to have quite the same functionality as that in ADF.
for example: in ADF dataflow pivot it has these options and not sure how to replicate these into dataflow gen2 in fabric
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.
16 Replies
I'm not familiar with ADF dataflows.
Could you visualize what you want to achieve?
E.g. create some small dummy data in Excel and visualize how you want that data to be pivoted and grouped or aggregated.
please see attached the sample data showing initial data and the required data.
There does not seem to be an optioni to include a sample file so I attached the screen-shots.
hopethis helps
thank you
Something like this?
let 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") in #"Pivoted column"How do I use your code?
Can you guide me to create this ib the dstafkow
Tgankyiu
Inside the Dataflow Gen2, you can click Get Data -> Blank query, and then paste the code inside there, then click Next.
