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

     

     

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"