Forum Discussion
Populate Column Field on Fact Table with data from Multiple Tables based on priority in Power BI
Hi Anonymous ,
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
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
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!