Forum Discussion
Combining data from seperate queries
Hello!
I am needing help with the combining data from two different queries into one table.
I tried to creat a relationship between the two and that did not help. Basically, it is last month data in a seperate query and also current month in a seperate query. It is that way for a reason or else I would of combined the queries in SQL.
The goal is to show the amount collected last month in a bar chart and the ammount collected in the current month in the same visual. Unfortunately, when I combine them into one table. I get a blank column and not both of the months.
Thank you in advance for your help!!
Hi @Anonymous ,
We can try appending two tables as a new query after creating a custom column for them:
All inquiries are here:
Current dlq collection:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQwUNJRMjIwMtA11DVFMA2VYnWilYwRssa65ggmUDYWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [amount_collected = _t, date = _t, start_of_month = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"amount_collected", Int64.Type}, {"date", type date}, {"start_of_month", type date}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Category", each "CurrentMonth") in #"Added Custom"Last month DLQ Collecte:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjVQ0lEyMjAy0DXUtUQwDZVidaKVDI3hssa6pggmUDYWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [amount_collected = _t, date = _t, start_of_month = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"amount_collected", Int64.Type}, {"date", type date}, {"start_of_month", type date}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Category", each "LastMonth") in #"Added Custom"Anexo1:
let Source = Table.Combine({#"Current DLQ Collecte", #"Last Month DLQ collected"}) in SourceIf it doesn't meet your requirement, could you show the exact expected result based on the tables we've shared?
Best regards
4 Replies
Have you tried using UNION to create a third table?
Anonymous
You can append the two tables: https://radacad.com/append-vs-merge-in-power-bi-and-power-query.
Create a common date dimension andTo get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/ use that for analysis.Hi @Anonymous ,
We can try appending two tables as a new query after creating a custom column for them:
All inquiries are here:
Current dlq collection:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQwUNJRMjIwMtA11DVFMA2VYnWilYwRssa65ggmUDYWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [amount_collected = _t, date = _t, start_of_month = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"amount_collected", Int64.Type}, {"date", type date}, {"start_of_month", type date}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Category", each "CurrentMonth") in #"Added Custom"Last month DLQ Collecte:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjVQ0lEyMjAy0DXUtUQwDZVidaKVDI3hssa6pggmUDYWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [amount_collected = _t, date = _t, start_of_month = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"amount_collected", Int64.Type}, {"date", type date}, {"start_of_month", type date}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Category", each "LastMonth") in #"Added Custom"Anexo1:
let Source = Table.Combine({#"Current DLQ Collecte", #"Last Month DLQ collected"}) in SourceIf it doesn't meet your requirement, could you show the exact expected result based on the tables we've shared?
Best regards
