Populate Column Field on Fact Table with data from Multiple Tables based on priority in Power BI

[mcastro70]

Hi,

Sorry if I could not be more specific with my question. I am a basic Power BI user and need help on achieving/resolving the below issue. The image below contains notes explaning more or less what I am looking for. Any help will be greatly appreciated. Should have any further questions (I am sure you will), you can contact me directly  @   mauri_ca_99@yahoo.com

[v-stephen-msft · Accepted solution]

Hi @mcastro70 ,

Could you tell me if your problem has been solved?
If it is, kindly Accept it as the solution. More people will benefit from it.
Or you are still confused about it, please provide me with more details about your problem.

Best Regards,
Stephen Tao

[watkinnc]

You need a consultant.

-- Nate 🙂

[wdx223_Daniel]

asume all the dimtable only have those 6 columns and the column sort is identical, then try below code

NewStep=Table.SplitColumn(Table.AddColumn(FactTable,"n",each let a=List.Zip(List.Transform({DimFinancialData1,DimFinancialData2,DimFinancialData3,DimFinancialData4},each Record.ToList(_{[Serial Number=[Serial Number]]}? ??[]))) in List.Transform(List.Positions(a),each if _=4 then List.Sum(a{_}) else List.Skip(a{_}_,(x)=>x=null or x=""){0}?) ,"n",each _,{"PO Number","Invoice Number","Lease Schedule Number","Net Values","Cost Center"})

[mcastro70]

Hi Dan;

Thanks so much for prompt reply.

Like I mentioned in my post, I am a basic user in Power BI and for me to enter codes will need a little more hand-holding than most users. I can see the obvious which is  that in the code I have to replace the Dim and Fact table names with the actual Table Names in my Data Model; however, I do not know where to enter the your code. Is this in the advance editor for the Fact Table to add a new column or is this a brand new table that would be created out of it. Also, do I have to input/raplace any more values within those prentheses or brackets?

And just to clarify in case is not well explained in my comments; I need one row for each Serial Number that matches when bumped against the DimTables and this could mean that the row data on those 5 new columns on the FactTable (Data brought from DimFinancialData) may contain values from all 4 DimTables. For example; if the P.O is black in Dim1 then get from Dim2, if also blank then get it from Dim 3 etc... same applies for the other columns except for the "Net Value" which I need to read from all 4 Dims and consolidate the total value if the Serial Number is match.

I am so sorry If I sound too basic!

Thanks again!

[watkinnc]

This can be done in a few steps.  First, I would combine the four dimension tables.  I would also delete the last five empty columns (Data brought from DimFinancialData). Now we have just the four columns in FactOperational Data.  Then it's not too hard Make sure that you pay careful attention to the placement of your brackets, braces, and parentheses. There is a lot of M syntax crammed into the last expression.  Also, the optional table type and field type record is optional, but it'll run much faster if you keep it in.  

Suppose your current final step on the fact table is named FinalStep.  We'll add a step called DimCombined.  In the formula bar, type:

= Table.Combine({DimFinancialData1, DimFinancialData2, DimFinancialData3, DimFinancialData4})

Now we can refer to that table in the next expression, but we are still going to refer to the original FinalStep first.  Add a step named NewTable, and in the formula bar, type:

= Table.AddColumn(FinalStep, "NewColumn", each Table.FromRows({List.Union(Table.ToRows(Table.FindText(DimCombined, [Serial Number])[[PO Number], [Invoice Number], [Lease Schedule Number], [Net Value], [Cost Center]]))}), type table [PO Number = text, Invoice Number = number, Lease Schedule Number = text, Net Value = Currency.Type, Cost Center = text])

That's it!

--Nate

[mcastro70]

Hi Nate,

Thanks so much for taking the time to look into my question.

Unfortunately; The 4 DimTables cannot be combined as they are four different and huge financial databases for IT asset Management and there are lots more columns than just those 5 in my case. My case would be just on of the reports/views from probably dozens of other reports that will come in later. Of course we will work on some sort of normalization to make things alot smoother where the column headers to be used on all reports will be most likely renamed the same on all 4 Finan Databases.

However; for the sake of me learning things like your suggestion; I still went ahead and did but all rows came out as blank/null. I probably did something wrong 🤔

Thanks Again!