topic Re: ADF pivot vs fabric dataflow pivot in Data Engineering

ADF pivot vs fabric dataflow pivot

arkiboys2 — Sat, 26 Oct 2024 08:22:05 GMT

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

Re: ADF pivot vs fabric dataflow pivot

frithjof_v — Sat, 26 Oct 2024 08:56:21 GMT

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.

Re: ADF pivot vs fabric dataflow pivot

arkiboys2 — Sat, 26 Oct 2024 10:01:13 GMT

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

Re: ADF pivot vs fabric dataflow pivot

frithjof_v — Sat, 26 Oct 2024 10:42:12 GMT

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"

Re: ADF pivot vs fabric dataflow pivot

arkiboys2 — Sat, 26 Oct 2024 15:47:16 GMT

How do I use your code?

Can you guide me to create this ib the dstafkow

Tgankyiu

Re: ADF pivot vs fabric dataflow pivot

frithjof_v — Sat, 26 Oct 2024 15:55:59 GMT

Inside the Dataflow Gen2, you can click Get Data -> Blank query, and then paste the code inside there, then click Next.

Re: ADF pivot vs fabric dataflow pivot

arkiboys2 — Sat, 26 Oct 2024 16:19:45 GMT

I am now implementing your suggestion on the whole actual data. but not sure why the remove duplicates only returns one row whereas it should return four rows similar to the example I sent you

Re: ADF pivot vs fabric dataflow pivot

frithjof_v — Sat, 26 Oct 2024 16:35:39 GMT

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"}

Re: ADF pivot vs fabric dataflow pivot

arkiboys2 — Sat, 26 Oct 2024 17:30:41 GMT

for the unpivot, which columns do I need to select first? thanks

Re: ADF pivot vs fabric dataflow pivot

frithjof_v — Sat, 26 Oct 2024 18:09:43 GMT

I'm guessing all the date columns

Re: ADF pivot vs fabric dataflow pivot

arkiboys2 — Sat, 26 Oct 2024 18:31:33 GMT

in your example, you do not have title date in unpivot result whereas you have title date in group by result. is this wrong?

Re: ADF pivot vs fabric dataflow pivot

frithjof_v — Sat, 26 Oct 2024 19:37:52 GMT

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"

Re: ADF pivot vs fabric dataflow pivot

arkiboys2 — Sat, 26 Oct 2024 21:04:19 GMT

1- struggling to find the custom function in the menu and how to use it

2- and is it necessary to add a new row? there are alot of columns for me to add in the real data?

3-and since title date is not showing if no dummy row is added, how about replace the title date null values with na instead of adding a row?

Re: ADF pivot vs fabric dataflow pivot

frithjof_v — Sun, 27 Oct 2024 06:09:55 GMT

3 - yep, you could try that. Perhaps that also solves 1 and 2?

Ultimately, there are probably several ways to solve the case, depending on your specific needs.

The code I made was my initial thought, based on the sample data / screenshots provided.

In essence, I think the case is about pivot with multiple value columns. Googling for "Power Query pivot with multiple value columns" might provide useful answers.

You can also consider posting in the Power BI -> Power Query forum, since the case is about using Power Query to transform the data:

https://community.fabric.microsoft.com/t5/Power-Query/bd-p/power-bi-services

It is easier to provide guidance when knowing the full nature of your actual data (e.g. knowing all columns) and more details about your specific needs. However I totally understand if you cannot share more details about the actual data online due to business confidentiality, etc.

Re: ADF pivot vs fabric dataflow pivot

arkiboys2 — Sun, 27 Oct 2024 05:54:59 GMT

did you create the custom function by hand or via menu?

I tried adding the dummy row but had to copy paste your code in the script section

Re: ADF pivot vs fabric dataflow pivot

frithjof_v — Sun, 27 Oct 2024 06:19:14 GMT

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.

Re: ADF pivot vs fabric dataflow pivot

arkiboys2 — Sun, 27 Oct 2024 06:44:53 GMT

you have been a wonderful help. thanks ever so much.