Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I have three separate excel files I'm importing into a model.
I want to bring that data together. So I want to show a list of all feedback which has this information:
Which I obviously have to pull from all threee sources and amalgamate.
Can this be done, and how do I go about it?
Solved! Go to Solution.
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"
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"
Thank you very much.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
97 | |
80 | |
69 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |