Help with Merger queries or Relationship between two fact tables

[Priya]

Hi Team,

I’m not sure how to explain my issue in correct format. so i try to explain this in simple way.

let’s say i have two Fact tables table1; table2. i tried to do in 2 different ways

Merge Queries:
Join between Fact to Fact table.
Table1 Col names: Facility Code; Date;Original,Apportion,Actual .

Table2 Colnames: Facility Code;Date;Capabilitybbls

Merger Queries: i try to Merge table1 with Table2 using full outer join.(i want everything from table1 and as well as table2 selected column) Joining on Facility Code from table1 with Facility Code of table2.

Note: why I want is… there are few facility codes for that date in table1 that are not in table2 viceversa.

My Need: ALL I want is to create table visual and get all table1 columns and only CapabilityM3 column from table2.

Capability M3 is a Calculated Column that uses this logic below

                  CapabilityM3: Capabilitybbls/6.289* (No of days in a month).

My Issue: after doing Merge the joining condition is not showing up correctly in table visual.

Example:

Table1:

Table2:

Result Table3:(I want this to show in my dashboard)

But the Result in my dashboard table is showing this:

Facility Code For Banana for 1/1/2019 is Missing. when i do a join between facility code on table1 & table2 it is considering only one Facility Code of table1. even though doing full outer Join.

so i taught if we do join on both facility code and Date column it shows just for

banana 1/1/2019 1380187.63 0 0 0

not Both for Facility Code “Banana”:

banana 1/1/2019 1380187.63 0 0 0
banana 3/1/2020 1380187.63 15000 20000 25000
I hope I made it clear. any help on this is much appreciated.

Same thing without Using Merge i tried using Relationship between Table1 and Table2 to joining on Facility code and Table1 joined to my Date dimension table.

Help me getting the Result table 3

Thank You.

[MudassirAli]

@Priya It would be really helpful if you can share your excel file and pbix file. Without the data, it’s very difficult to provide the accurate solution.

Thanks.

[EnterpriseDNA]

Hi @Priya, we aim to consistently improve the topics being posted on the forum to help you in getting a strong solution faster. While waiting for a response, here are some tips so you can get the most out of the forum and other Enterprise DNA resources.

• Use the forum search to discover if your query has been asked before by another member.

• When posting a topic with formula make sure that it is correctly formatted to preformatted text </>.

• Use the proper category that best describes your topic

• Provide as much context to a question as possible.

• Include the masked demo pbix file, images of the entire scenario you are dealing with, screenshot of the data model, details of how you want to visualize a result, and any other supporting links and details.

I also suggest that you check the forum guideline How To Use The Enterprise DNA Support Forum. Not adhering to it may sometimes cause delay in getting an answer.

Please also check the How To Mask Sensitive Data thread for some tips on how to mask your pbix file

[Priya]

Hi, Thank you for the response.

Here is the Pbix file.Test_EDNA.pbix (1.1 MB)

Here is the Data from Excel Spreadsheet.
Delivery Facility Nom’s 2020.csv (1.1 MB) Delivery Facility Nom’s.csv (1.5 MB) (2019)

I have appended two tables into one fact table1(2019table1 to 2020table2)
second fact table is below screenshot:

All i need is a Matrix table with System name, Facility Code, Capability M3 , Originalvol, ApportionedVol, ActualVol.

CapabilityM3 is custom column created in Power Query:

when i created this CapabilityM3 should show for all the business dates which is missing for Facility Code type “Banana”.

1. first step i tried to do is Append 2019([Delivery Facility Nom’s.csv|attachment] to 2020 [Delivery Facility Nom’s 2020.csv|attachment] as fact table1

2. Mergerd Queries: i merged fact table1 to Fact table 2(capability) to get System name , Capability(BBL) from Capability table(Fact table2)

I tried to show two ways by Relationship between two fact tables and the other is by Merge Queries.

Both ways result are same Missing CapabilityM3 for Facility Code “Banana”. for all the business dates(2019/2020)
how can i fix this discrepancy?

For 2019 capabilityM3 is should be based on the Custom logic showing for all business month Date.
there is no original, apportioned, actual volume. for Facility code" banana"
but For 2020 facility code “banana” capabilityM3, original, apportioned, actual volume is available for just 6/1/2020. but we need to show at least capability M3 for all the Business_month 2020

I hope i made it clear.

Thanks,
Priya

[samaguire]

@Priya

but we need to show at least capability M3 for all the Business_month 2020

If I understand you correctly, you want to show the CapabilityM3 value for all months in year 2020.

That being the case, the easiest solution would be to use your original solution but have the input data that contributes to “Delivery Facility Nom’s” table include rows for all months in 2020 - perhaps where the ‘ACTUALVOL’ is 0

Is it possible to get this data included?

[ibesmond]

@Priya.

This is my first attempt to share a solution since I got my membership, but I’ll give it a try.

I think you may want to start off by renaming your column names. As far as I could tell, you have two columns with different Header names. Class and Commodity ID. Rename those two Headers in your 2019 data so all the header names match.

Next select the append queries. You can either append one of the existing tables or append them into a new table. (I prefer to use new tables - but I don’t think there is much of a difference, unless you are considering size). It will basically stack the two tables into one table.

At this point you can disable the load (uncheck the enable load) for these tables. And you are left with the one table that you can then start building your report with.

Hope this helps.

eDNA Forum- Count Delimited Text - Solution.pbix (411.0 KB)

If anyone thinks of a better solution please @ibesmond me so I will get notified.

[MudassirAli]

@Priya

First of all you have to change the heading name from DELIVERY FACILITY to Facility Code & HAUL to HAIL in Delivery Facility Nom’s Table before Append. The concept is to have same table heading names before appending them otherwise the unmatched heading will show as separate columns. That’s why after Appending you were getting DELIVERY FACILITY and Facility Code in different columns when actually both are the same.

Secondly the Effective Date in Capability Table was not in a Date Format. Therefore, I extracted only Date from that column using the Extract then Text Before Delimiter, using space as delimiter and then converted the column to Date Type.

The reason why you are not getting the values in Banana in 2019 is because your 2019 Fact Table does not contain any value at all related to Code Banana that’s why you are not getting the relevant values after Merging them.

I don’t know whether your dataset is complete or not but the steps I mentioned above would have given you the desired result if the fact table had contained data related to Code Banana.

Thanks.

[Priya]

Hi @samaguire,

Thank you for your response.

It’s a good point but right now I’m not sure if we can include data but I have to confirm with my team.
Let’s say if we include data for all 2020 where “ACTUALVOL” is 0. That gives my capabilityM3 for all 2020. Which is fine. That solves my one of my issue.
How about for all months 2019?
If you look at 2019 for facility code “banana” there is no ORIGINALVOL, APPORTIONEDVOL, ACTUALVOL but based on C.capability(BBL) for banana for 1/1/2019 of Capability table. based on this i have to show capabilityM3 conversions for all business months. For other facility code it is showing except for this.
Any thoughts on this?
I know i don’t have any related data for 2019 for “banana” in my fact table1. All I need to show is CapabilityM3 for all business months. For both 2019 and 2020.

Data is sensitive I have to create a sample excel spreadsheet and pbix file. But right now this is the point we’re I find myself stuck with.

Much appreciate for taking time and looking into this.

-Priya

[Priya]

Thank you @ibesmond

Sorry, my bad I cannot share my original file data is sensitive. This is first time me sharing these sample files is quite a work around for me to provide sample pbix file and excel spreadsheet.
I tried my best to come up with sample didn’t had a chance to look back on my column names.

Excel spreadsheet files may be not clear with column names. I will take a look. Thank you.

[Priya]

Thank you @MudassirAli,

As I mentioned above data is sensitive cannot upload my original file. I try to come up sample files. Sorry my bad for discrepancy in column names. If you see my steps in power query that I sent I have applied those changes.
And since it’s just a sample dataset and all am trying to get is
From Jan 2019 to Dec2019 for facility code “banana “ we need to show CapabilityM3 values and for ORIGINALVOL, APPORTIONEDVOL, ACTUALVOL is 0

For 2020 for “banana” capabilityM3 for all business months and except 6/1/2020 I do have ACTUALVOL.
As @samaguire suggested I can try asking my team to include data for all 2020 months where ACTUALVOL is 0.
This might give me capabilityM3 for all business months for 2020.

For 2019 as you mentioned I don’t have any related data values that’s the issue.
Based on C. capability (BBL) from capability table and the logic that I created using custom column can I not get just CapabilityM3 for 2019?

At this point having this situation what can we do to achieve this. Original dataset is complete and it’s coming from Toad.

Thank you.

[samaguire]

So, in that case you need to cross join the months with the facilites, then link each of the fact tables to the artificial table.

I did this purely in PQ (though you could use DAX) in Excel for you based on the data in your PBIX file. You get the below output in a pivot table.Book1.xlsx (386.6 KB) Book1.pq (2.0 KB)

Power Query clipboard data:

// tbl_DeliveryFacilityNoms
let
    Source = Excel.CurrentWorkbook(){[Name="tbl_DeliveryFacilityNoms"]}[Content]
in
    Source

// tbl_Capability
let
    Source = Excel.CurrentWorkbook(){[Name="tbl_Capability"]}[Content]
in
    Source

// tbl_Date
let
    Source = Excel.CurrentWorkbook(){[Name="tbl_Date"]}[Content]
in
    Source

// Facility Code
let
    Source = List.Distinct(List.Combine({tbl_DeliveryFacilityNoms[#"Facility Code"],tbl_Capability[#"Facility Code"]}))
in
    Source

// Month & Year
let
    Source = List.Distinct(tbl_Date[#"Month & Year"])
in
    Source

// DeliveryFacilityNomsWithCapability
let
    Source = #"Month & Year",
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Month & Year"}}),
    #"Added Custom" = Table.AddColumn(#"Renamed Columns", "Facility Code", each #"Facility Code"),
    #"Expanded Facility Code" = Table.ExpandListColumn(#"Added Custom", "Facility Code"),
    #"Merged Queries" = Table.NestedJoin(#"Expanded Facility Code", {"Facility Code"}, tbl_Capability, {"Facility Code"}, "tbl_Capability", JoinKind.LeftOuter),
    #"Expanded tbl_Capability" = Table.ExpandTableColumn(#"Merged Queries", "tbl_Capability", {"Effective Date", "Facility Type", "Capability(BBL)", "Capability1(m3)"}, {"Effective Date", "Facility Type", "Capability(BBL)", "Capability1(m3)"}),
    #"Merged Queries1" = Table.NestedJoin(#"Expanded tbl_Capability", {"Month & Year", "Facility Code"}, tbl_DeliveryFacilityNoms, {"BUSINESS_MONTH", "Facility Code"}, "tbl_DeliveryFacilityNoms", JoinKind.LeftOuter),
    #"Expanded tbl_DeliveryFacilityNoms" = Table.ExpandTableColumn(#"Merged Queries1", "tbl_DeliveryFacilityNoms", {"CLASS", "C_ID", "HAIL", "RECEIPT_SIT", "SORTORDER", "ORIGINALVOL", "APPORTIONEDVOL", "ACTUALVOL"}, {"CLASS", "C_ID", "HAIL", "RECEIPT_SIT", "SORTORDER", "ORIGINALVOL", "APPORTIONEDVOL", "ACTUALVOL"})
in
    #"Expanded tbl_DeliveryFacilityNoms"

[Melissa]

Hi @ibesmond,

Great to have you join in and share what you’ve learned with the community!

Just wanted to let you know that you can set notifications, if you’d like.

1. Click on your badge
2. Click on your name, select Preferences and select Preferences again on the left hand side.

Set the options to your personal preference and Save changes.
See you around!

[EnterpriseDNA]

A response on this post has been tagged as “Solution”. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the check box. Thanks!

[BrianJ]

@ibesmond,

@ibesmond

This is my first attempt to share a solution since I got my membership, but I’ll give it a try.

. Big kudos for taking the leap on your first solution posted. I think you’ll find it is an incredible way to sharpen your own skills and help other people in the process. I look forward to seeing more of your solutions here in the future. Warning: it’s highly addictive…

– Brian