Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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.
Solved! Go to 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!
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!
You can achieve that by writing the functions as you described. Where are you stuck? If you provide sample data we can assist better.
Check out the July 2025 Power BI update to learn about new features.
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.