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.