Creating a conditional table from other tables

[cgyan9]

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!  

[p45cal]

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"

[lbendlin]

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

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

[cgyan9]

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

Month Yr	Location	Function	Hrs
Jan-24	A	AA	1
Jan-24	B	BB	2
Mar-24	C	CC	3
Apr-24

Table 2 -

Month Yr	Location	Function	Hrs
Mar-24	C	CC	1
Apr-24	B	BB	2
May-24	C	CC	2

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

Month Yr	Location	Function	Hrs
Jan-24	A	AA	1
Jan-24	B	BB	2
Mar-24	C	CC	3
Apr-24	B	BB	2
May-24	C	CC	2

[lbendlin · Accepted solution]

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