Combining Different Tables for Reporting

[Ice1341]

Hi, 

I have combined two different tables. My goal is to pull the monthly budget vs actual sales. I can pull the data in separate reports but cannot combine into one report. The actual sales data keeps repeating for every month this year- even though the report only has Jan Sales. I have created a separate date table and I still can't get the report to work. Please help. 

[v-deddai1-msft]

Hi @Ice1341 ,

Would you please show us some sample data by onedrive for business? Then we can help you more correctly. 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Best Regards,

Dedmon Dai

[StefanoGrimaldi]

if you have one table for budget another for the real sales, you dont need to merge them in one, keep them separate and them do a relantionship between them, for a better answer I reccomend you post a sample of both tables or a pbix of it with dummy data on it

[Ice1341]

The tables are separate but I want to pull from them both for a report. I built a relationship based on customer name and dates but somehow the actuals repeat the same actuals sales data for Jan every month.. it should be zero since no Feb. sales have occurred.  

[smpa01]

@Ice1341  You can append both the sales and budget table to uniqueid table and create 1-many/1-1 relation with UQID~sales and UQID~budget. It is a star schema and once you build your measure the expanded table at the backend would be able to show you everything.

e.g.

//budget table
let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtQ31DcyMDJU0lHKAxGGBgZKsTqY4kZYxI2AhDFIPBYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Name = _t, Budget = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Name", type text}, {"Budget", Int64.Type}}),
    #"Inserted Merged Column" = Table.AddColumn(#"Changed Type", "uqid", each Text.Combine({Text.From([Date], "en-US"), [Name]}, "-"), type text)
in
    #"Inserted Merged Column"

//sales

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtQ31DcyMDJU0lHKAxGGBgZKsTqY4kZwcSO4uBGQMAaJxwIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Name = _t, Sales = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Name", type text}, {"Sales", Int64.Type}}),
    #"Inserted Merged Column" = Table.AddColumn(#"Changed Type", "uqid", each Text.Combine({Text.From([Date], "en-US"), [Name]}, "-"), type text)
in
    #"Inserted Merged Column"

//uqid
let
    Source = List.Combine({{sales[uqid]},{budget[uqid]}}),
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandListColumn(#"Converted to Table", "Column1"),
    #"Removed Duplicates" = Table.Distinct(#"Expanded Column1"),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Duplicates",{{"Column1", "uqid"}})
in
    #"Renamed Columns"