Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

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

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 ...
Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    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

wdx223_Daniel's avatar
wdx223_Daniel
Community Champion
4 years ago

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"})

Anonymous's avatar
Anonymous
Not applicable
4 years ago

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!