Function to invoke function and append the outcome tables

[Anonymous]

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.

[v-kelly-msft · Accepted solution]

Hi  @Anonymous ,

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

Then append tables in either one of the table:

And you will see:

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:

For the related .pbix file,pls see attahced.

Best Regards,
Kelly

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

[lbendlin]

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