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 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. 

Need Help
Tutorial Requests

4 Replies

  • 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"

  • v-deddai1-msft's avatar
    v-deddai1-msft
    Community Support
    5 years ago

    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