Forum Discussion

Mic1979's avatar
Mic1979
Post Partisan
1 year ago
Solved

Dynamic Expand Power Query Custom Function

Dear all,   I have two tables to merge. Here two samples table: Table_to_Change   and  Start_Table (this table as other columns that the ones represented here...) What I need is t...
Need Help
  • v-nmadadi-msft's avatar
    v-nmadadi-msft
    1 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

wdx223_Daniel's avatar
wdx223_Daniel
Community Champion
1 year ago

in your function code, the "Value" Column was created by NestedJoin in the step of Merge_Start_Table, and the next step refers Euro_Value, which is "New" come from the function's last arguement,  to expand, that is the error.

you can amend your code as this 

let
Source = ConsumptionReview_2(Base_Price_EURO, Pipe_Size_Adder_PERC, "Region", "Type", "Pipe_Size", "Region", "Type", "Pipe_Size", "Value")
in
Source

or just delete the last arguement and amend your function code of step "Expanded_Merged_Table" as this

Expanded_Merged_Table = Table.ExpandTableColumn (
Merge_Start_Table,
"Value",
List.Select(Table.ColumnNames(Clean_Start_Table), each Text.Contains(_, "€")),
List.Select(Table.ColumnNames(Clean_Start_Table), each Text.Contains(_, "€"))
)
Mic1979's avatar
Mic1979
Post Partisan
1 year ago

Hello wdx223_Daniel

thanks.

 

This is the working solution:

 

let Source = ConsumptionReview_2(Base_Price_EURO, Pipe_Size_Adder_PERC, "Region", "Type", "Pipe_Size", "Region", "Type", "Pipe_Size", "Value") in Source

 

let Source = ConsumptionReview_2(Base_Price_EURO, Pipe_Size_Adder_PERC, "Region", "Type", "Pipe_Size", "Region", "Type", "Pipe_Size", "Value") in Source

 

Thanks again.