Forum Discussion
Dynamic Expand Power Query Custom Function
- Anonymous1 year ago
Hi Mic1979 ,
Thanks for reaching out to the Microsoft fabric community forum.
There may be a possibility that you are encountering data type mismatch, consider changing the data type from percentage to decimal number and then multiplying them and check if you are still encountering the issue.
Also consider using error handling https://learn.microsoft.com/en-us/power-query/error-handling
to test and catch errors in your code.
If you find this post helpful, please mark it as an "Accept as Solution" and consider giving a KUDOS.
Thanks and Regards
Hello,
what I need now is to multiply the column with % in header and column with € in header .
I made the following function:
(
Start_Table,
Table_to_Change,
Start_Table_Col_1 as text, Start_Table_Col_2 as text, Start_Table_Col_3 as text,
Change_Column_1 as text, Change_Column_2 as text, Change_Column_3 as text,
Euro_Value as text) =>
[
Start_Table_Helper = Table.AddColumn (Start_Table, "Start_Table_Helper", each Text.Combine({
Record.Field(_, Start_Table_Col_1),
Record.Field(_, Start_Table_Col_2),
Record.Field(_, Start_Table_Col_3)}, " "), type text),
Clean_Start_Table = Table.RemoveColumns (
Start_Table_Helper,
List.Select(Table.ColumnNames(Start_Table_Helper), each not (Text.Contains(_, "Helper") or Text.Contains(_, "€")))
),
Table_to_Change_Helper = Table.AddColumn (Table_to_Change, "Table_to_Change_Helper", each Text.Combine({
Record.Field(_, Change_Column_1),
Record.Field(_, Change_Column_2),
Record.Field(_, Change_Column_3)}, " "), type text),
Clean_Table_to_Change = Table.RemoveColumns (
Table_to_Change_Helper,
{Change_Column_1, Change_Column_2, Change_Column_3}
),
Changed_Type_Table_to_Change = Table.TransformColumnTypes(
Clean_Table_to_Change,
List.Transform(List.Select(Table.ColumnNames(Clean_Table_to_Change), each Text.Contains(_, "%")), each {_, Percentage.Type})
),
Merge_Start_Table = Table.NestedJoin(
Changed_Type_Table_to_Change,
{"Table_to_Change_Helper"},
Clean_Start_Table,
{"Start_Table_Helper"},
"Value",
JoinKind.Inner
),
Expanded_Merged_Table = Table.ExpandTableColumn (
Merge_Start_Table,
Euro_Value,
List.Select(Table.ColumnNames(Clean_Start_Table), each Text.Contains(_, "€")),
List.Select(Table.ColumnNames(Clean_Start_Table), each Text.Contains(_, "€"))
),
Result = Table.AddColumn (
Expanded_Merged_Table,
"Result",
each (x,y,z) =>
each if Table.ColumnNames(y, each Text.Contains(_, "%"))< 0
then z = Table.ColumnNames(x, each Text.Contains(_, "€"))*(1+Table.ColumnNames(y, each Text.Contains(_, "%")))
else if Table.ColumnNames(y, each Text.Contains(_, "%"))> 0
then z = Table.ColumnNames(x, each Text.Contains(_, "€"))*(1-Table.ColumnNames(y, each Text.Contains(_, "%")))
else "")
]
[Result]
I got the following:
How to get the calculation in the column Result instead of Function?
Thanks.