Forum Discussion
Linking data sources and comparing
I have three separate excel files I'm importing into a model.
- Results of a survey. (reference no, rating, comments)
- List of faults fixed (reference no, job category, who fixed it)
- List of requests (reference no, job category, who fixed it)
I want to bring that data together. So I want to show a list of all feedback which has this information:
- Reference no
- Rating
- Comments
- Job category
- Who fixed it
Which I obviously have to pull from all threee sources and amalgamate.
Can this be done, and how do I go about it?
You can either use merge function when you import your tables in to power BI or else after importing you can build a relationship with all 3 tables under relationship tab.however, you must have a common field in all of your tables and if you have more than one (duplicates) you may have to create a new table without duplicate and use it as an intermediary table to relate two tables.
Screen shot of building relationships.
Double click on the connecting line for more options.
Please see the below code on importing data and merging.
let Source = Excel.Workbook(File.Contents("C:\Users\Dilumd\OneDrive - Brandix Lanka Pvt Ltd (1)\Brandix Data\PowerBi\Help\Power BI Help.xlsx"), null, true), #"List of faults fixed_Sheet" = Source{[Item="List of faults fixed",Kind="Sheet"]}[Data], #"Promoted Headers" = Table.PromoteHeaders(#"List of faults fixed_Sheet", [PromoteAllScalars=true]), #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"reference no", type text}, {" job category", type text}, {" who fixed it", type text}}), #"Merged Queries" = Table.NestedJoin(#"Changed Type",{"reference no"},#"List of requests",{"reference no"},"NewColumn",JoinKind.LeftOuter), #"Expanded NewColumn" = Table.ExpandTableColumn(#"Merged Queries", "NewColumn", {" who fixed it"}, {"NewColumn. who fixed it"}), #"Merged Queries1" = Table.NestedJoin(#"Expanded NewColumn",{"reference no"},#"Results of a survey",{"reference no"},"NewColumn",JoinKind.LeftOuter), #"Expanded NewColumn1" = Table.ExpandTableColumn(#"Merged Queries1", "NewColumn", {" rating", " comments"}, {"NewColumn. rating", "NewColumn. comments"}) in #"Expanded NewColumn1"
2 Replies
You can either use merge function when you import your tables in to power BI or else after importing you can build a relationship with all 3 tables under relationship tab.however, you must have a common field in all of your tables and if you have more than one (duplicates) you may have to create a new table without duplicate and use it as an intermediary table to relate two tables.
Screen shot of building relationships.
Double click on the connecting line for more options.
Please see the below code on importing data and merging.
let Source = Excel.Workbook(File.Contents("C:\Users\Dilumd\OneDrive - Brandix Lanka Pvt Ltd (1)\Brandix Data\PowerBi\Help\Power BI Help.xlsx"), null, true), #"List of faults fixed_Sheet" = Source{[Item="List of faults fixed",Kind="Sheet"]}[Data], #"Promoted Headers" = Table.PromoteHeaders(#"List of faults fixed_Sheet", [PromoteAllScalars=true]), #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"reference no", type text}, {" job category", type text}, {" who fixed it", type text}}), #"Merged Queries" = Table.NestedJoin(#"Changed Type",{"reference no"},#"List of requests",{"reference no"},"NewColumn",JoinKind.LeftOuter), #"Expanded NewColumn" = Table.ExpandTableColumn(#"Merged Queries", "NewColumn", {" who fixed it"}, {"NewColumn. who fixed it"}), #"Merged Queries1" = Table.NestedJoin(#"Expanded NewColumn",{"reference no"},#"Results of a survey",{"reference no"},"NewColumn",JoinKind.LeftOuter), #"Expanded NewColumn1" = Table.ExpandTableColumn(#"Merged Queries1", "NewColumn", {" rating", " comments"}, {"NewColumn. rating", "NewColumn. comments"}) in #"Expanded NewColumn1"