Forum Discussion

Ice1341's avatar
Ice1341
Helper I
5 years ago

Combining Different Tables for Reporting

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 ke...
Need Help
Tutorial Requests
StefanoGrimaldi's avatar
StefanoGrimaldi
Resident Rockstar
5 years ago

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's avatar
Ice1341
Helper I
5 years ago

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's avatar
    smpa01
    Community Champion
    5 years ago

    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"