Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Anonymous
Not applicable

Function to invoke function and append the outcome tables

Hello,

 

I have 2 tables that are like below:

 

- Table 1 list out volume of Product A and B last year by month

- Table 2 list out volume of Product A and B this year by month

 

I the tables are pivoted and stacked strangely, as they come from an in-house system. I have been able to create 2 functions, one to extract Product A by in either one of the 2 years, one to extract product B by either one of the two years.

 

Now if I apply these functions to table 1 and table 2, I will have 2x2 = 4 tables. But I only want 2 tables like:

- Table X list out volume of Product A both years by month

- Table Y list out volume of Product B both years by month

 

It means that I will want to append the product tables together after I invoked the functions. In general I would want to write a function:

 

Invoke 2 functions to table 

Append the tables just invoked to one another

 

 

How can I achieve this result?

 

Thank you before hand.

1 ACCEPTED SOLUTION
v-kelly-msft
Community Support
Community Support

Hi  @Anonymous ,

 

First add a column with the Year number respectively to both tables:

 

vkellymsft_0-1630304665406.png

Then append tables in either one of the table:

vkellymsft_1-1630304708338.png

And you will see:

vkellymsft_3-1630305378880.png

Then create a function as below:

 

let
    Source = (name) => let
        Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("TY+7CoNQEET/5dYW7vouDSGFkKRIKRZGJE1IRGIgf59dF2ZvMzDn6jKn70MbktCNL0kKQ2L9NN8lGf08rpIZertoz6P3n2SB3m16r4z6U7Ly/7eHZI1+mxfJBv06fXRPCnB5fxX4wuM8KbCJB1fIAMyBcgCToALALKiMvlANqvzo7kF1BFSEGr+xm3AKYCpMAObCvtRc2JeaC8vS4Q8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Product = _t, Month = _t, Volumn = _t]),
        #"Changed Type" = Table.TransformColumnTypes(Source,{{"Product", type text}, {"Month", type text}, {"Volumn", Int64.Type}}),
        #"Added Custom" = Table.AddColumn(#"Changed Type", "Year", each "Year 2021"),
        #"Appended Query" = Table.Combine({#"Added Custom", #"Year 2020"}),
        #"Product"=Table.SelectRows(#"Appended Query",each [Product]=name)
    in
        #"Product"
in
    Source

 

 Finally you will see:

vkellymsft_4-1630305445181.png

 

For the related .pbix file,pls see attahced.

 

Best Regards,
Kelly

Did I answer your question? Mark my reply as a solution!

View solution in original post

2 REPLIES 2
v-kelly-msft
Community Support
Community Support

Hi  @Anonymous ,

 

First add a column with the Year number respectively to both tables:

 

vkellymsft_0-1630304665406.png

Then append tables in either one of the table:

vkellymsft_1-1630304708338.png

And you will see:

vkellymsft_3-1630305378880.png

Then create a function as below:

 

let
    Source = (name) => let
        Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("TY+7CoNQEET/5dYW7vouDSGFkKRIKRZGJE1IRGIgf59dF2ZvMzDn6jKn70MbktCNL0kKQ2L9NN8lGf08rpIZertoz6P3n2SB3m16r4z6U7Ly/7eHZI1+mxfJBv06fXRPCnB5fxX4wuM8KbCJB1fIAMyBcgCToALALKiMvlANqvzo7kF1BFSEGr+xm3AKYCpMAObCvtRc2JeaC8vS4Q8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Product = _t, Month = _t, Volumn = _t]),
        #"Changed Type" = Table.TransformColumnTypes(Source,{{"Product", type text}, {"Month", type text}, {"Volumn", Int64.Type}}),
        #"Added Custom" = Table.AddColumn(#"Changed Type", "Year", each "Year 2021"),
        #"Appended Query" = Table.Combine({#"Added Custom", #"Year 2020"}),
        #"Product"=Table.SelectRows(#"Appended Query",each [Product]=name)
    in
        #"Product"
in
    Source

 

 Finally you will see:

vkellymsft_4-1630305445181.png

 

For the related .pbix file,pls see attahced.

 

Best Regards,
Kelly

Did I answer your question? Mark my reply as a solution!

lbendlin
Super User
Super User

You can achieve that by writing the functions as you described.  Where are you stuck?  If you provide sample data we can assist better.

Helpful resources

Announcements
July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.

Top Solution Authors