Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

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.

General Comment
Need Help
  • v-kelly-msft's avatar
    v-kelly-msft
    4 years ago

    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!

2 Replies

  • v-kelly-msft's avatar
    v-kelly-msft
    Community Support
    4 years ago

    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's avatar
    lbendlin
    Super User
    4 years ago

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