Forum Discussion

cgyan9's avatar
cgyan9
Regular Visitor
2 years ago
Solved

Creating a conditional table from other tables

Hi All, I am hoping to create a new table C with certain values from Table A or Table B -
Table C has the first column as Month - Year, the 2nd column should check the calculated measure of say hrs on a monthly basis from Table A and take that value else if there is no value for that particular month in Table A, it should take the calculated sum value in Table B for that particular month (Table B has values for all the months).
Thanks in advance for all the help!  

Need Help
  • lbendlin's avatar
    lbendlin
    2 years ago

     

    let
    Source = Table.SelectRows(#"Table 1", each ([Location] <> " ")) & #"Table 2",
    #"Removed Duplicates" = Table.Distinct(Source, {"Month Yr", "Location"})
in
    #"Removed Duplicates"

     

     

4 Replies

  • lbendlin's avatar
    lbendlin
    Super User
    2 years ago

    You posted this in the Power Query section.  Power Query does not support dynamic source creation. 

     

    In DAX you can create calulated tables, or temporary table variables for use in measures.

     

    Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

    Do not include sensitive information or anything not related to the issue or question.

    If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216

    Please show the expected outcome based on the sample data you provided.

    Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

    • cgyan9's avatar
      cgyan9
      Regular Visitor
      2 years ago

      Sharing an outline of the tables that I have -
      Table 1 

       

      Month YrLocationFunctionHrs
      Jan-24AAA1
      Jan-24BBB2
      Mar-24CCC3
      Apr-24   

       

      Table 2 -

       

      Month YrLocationFunctionHrs
      Mar-24CCC1
      Apr-24BBB2
      May-24CCC2

       

      Output Result Table - It should 1st check for values in Table 1, if blank in Table 1 then take values from Table 2 

      Month YrLocationFunctionHrs
      Jan-24AAA1
      Jan-24BBB2
      Mar-24CCC3
      Apr-24BBB2
      May-24CCC2

       

      • lbendlin's avatar
        lbendlin
        Super User
        2 years ago

         

        let
    Source = Table.SelectRows(#"Table 1", each ([Location] <> " ")) & #"Table 2",
    #"Removed Duplicates" = Table.Distinct(Source, {"Month Yr", "Location"})
in
    #"Removed Duplicates"

         

         

  • p45cal's avatar
    p45cal
    Solution Supplier
    2 years ago

    You have your table C set up with a year/month column,

    1. you merge it with table A in one column (let's give this column the header AA),

    2. you merge it with table B in another column (we'll head it with BB),

    3. you add another column (say headed Result) which examines column AA and if there's an empty table (no data in Table A for that year/Month) copies the BB column to itself, else copies the AA column to itself.

    4. Then remove columns AA and BB and

    5. process the Result column.

     

    Why don't you set up an Excel Workbook with a facsimile of these tables and provide a link to it so that someone can put some M-code together to demonstrate?

     

    Here's an example file: https://app.box.com/s/xahhaoc05meyghp81xabh1x0r52sr918

    Here's the code generated in it:

    let
    Source = DatesTable,
    #"Merged Queries" = Table.NestedJoin(Source, {"Year/Month"}, TableA, {"YearMonth"}, "AA", JoinKind.LeftOuter),
    #"Merged Queries1" = Table.NestedJoin(#"Merged Queries", {"Year/Month"}, TableB, {"YearMonth"}, "BB", JoinKind.LeftOuter),
    #"Added Custom" = Table.AddColumn(#"Merged Queries1", "Result", each if Table.IsEmpty([AA]) then [BB] else [AA]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"AA", "BB"}),
    #"Expanded Result" = Table.ExpandTableColumn(#"Removed Columns", "Result", {"Data1", "Data2"}, {"Data1", "Data2"})
in
    #"Expanded Result"