cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
abaak Frequent Visitor
Frequent Visitor

From 3 tables to one

Hi all

 

I have a main table where all the info for the items is. With links to other tables. I need to get 3 tables in the main table as columns. Can someone help me?

The data is shown like this:

 

table: item_attribute_type

itemAttributeTypeIdName
1Cosmetic Damage
2Privacy Data
3Defect

 

Table: item_attribute_value

itemAttributeTypeIditemAttributeValueIdValue
11None
12Medium
13Heavy
24No
25Yes
36No
37Yes

 

table: item_attribute 

itemIDitemAttributeTypeIditemAttributeValueId
113
125
137
211
224
236
312
324
336
411
424
437
511
525
536
612
625
636

 

 

At this moment the data is shown in a way that it is easy to add another attribute type. But to create a visual from this is for me very hard.

I want to have the columns like the following:

I know i need to creat the names of the columns myself. But how can I get the data from cosmetic damage for item 1 in the right column. probably with a LOOKUPVALUE but I cannot figure it out anymore.

Cosmetic DamagePrivacy DataDefectitemID
HeavyYesYes1
NoneNoNo2
MediumNoNo3
NoneNoYes4
NoneYesNo5
MediumYesNo6

 

Thanks in advance!

1 ACCEPTED SOLUTION

Accepted Solutions
richbenmintz
Advisor

Re: From 3 tables to one

Hi @abaak,

 

k, the simple solution

-> create two calculated columns in item_attribute table

Type Value = RELATED('Table1'[Name]) 
Value Name = RELATED(Table2[Value]) 

-> Use the Matrix Visualisation, place Item_id on rows, Type Value on Columns and Value Name on Values and you should see the following table

related column.PNG

 

View solution in original post

3 REPLIES 3
richbenmintz
Advisor

Re: From 3 tables to one

Hi @abaak,

 

--Update

Sorry I misunderstood the requirement, you are looking to pivot the data, this can be done with power query through a series of Pivots and Joins, the picture below show the results and the Power Query code shows how i got there

 

three tables to one.PNG

 

let
    Source = Excel.Workbook(File.Contents("C:\Users\rmintz\Downloads\Data for Comm.xlsx"), null, true),
    Table3_Table = Source{[Item="Table3",Kind="Table"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(Table3_Table,{{"itemID", Int64.Type}, {"itemAttributeTypeId", Int64.Type}, {"itemAttributeValueId", Int64.Type}}),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type",{"itemAttributeTypeId"},item_attribute_type,{"itemAttributeTypeId"},"item_attribute_type",JoinKind.LeftOuter),
    #"Expanded item_attribute_type" = Table.ExpandTableColumn(#"Merged Queries", "item_attribute_type", {"Name"}, {"Name"}),
    #"Pivoted Column" = Table.Pivot(#"Expanded item_attribute_type", List.Distinct(#"Expanded item_attribute_type"[Name]), "Name", "itemAttributeValueId"),
    #"Merged Queries1" = Table.NestedJoin(#"Pivoted Column",{"Cosmetic Damage"},item_attribute_value,{"itemAttributeValueId"},"item_attribute_value",JoinKind.LeftOuter),
    #"Expanded item_attribute_value" = Table.ExpandTableColumn(#"Merged Queries1", "item_attribute_value", {"Value"}, {"Value"}),
    #"Renamed Columns" = Table.RenameColumns(#"Expanded item_attribute_value",{{"Value", "Cosmetic"}}),
    #"Merged Queries2" = Table.NestedJoin(#"Renamed Columns",{"Privacy Data"},item_attribute_value,{"itemAttributeValueId"},"item_attribute_value",JoinKind.LeftOuter),
    #"Expanded item_attribute_value1" = Table.ExpandTableColumn(#"Merged Queries2", "item_attribute_value", {"Value"}, {"Value"}),
    #"Renamed Columns1" = Table.RenameColumns(#"Expanded item_attribute_value1",{{"Value", "Privacy"}}),
    #"Merged Queries3" = Table.NestedJoin(#"Renamed Columns1",{"Defect"},item_attribute_value,{"itemAttributeValueId"},"item_attribute_value",JoinKind.LeftOuter),
    #"Expanded item_attribute_value2" = Table.ExpandTableColumn(#"Merged Queries3", "item_attribute_value", {"Value"}, {"Value"}),
    #"Renamed Columns2" = Table.RenameColumns(#"Expanded item_attribute_value2",{{"Value", "Defect Value"}}),
    #"Removed Columns" = Table.RemoveColumns(#"Renamed Columns2",{"Cosmetic Damage", "Privacy Data", "Defect", "itemAttributeTypeId"}),
    #"Grouped Rows" = Table.Group(#"Removed Columns", {"itemID"}, {{"Cos", each List.Max([Cosmetic]), type text}, {"Priv", each List.Max([Privacy]), type text}, {"Def", each List.Max([Defect Value]), type text}}),
    #"Renamed Columns3" = Table.RenameColumns(#"Grouped Rows",{{"Priv", "Privacy Data"}, {"Cos", "Cosmetic Damage"}, {"Def", "Defect"}})
in
    #"Renamed Columns3"

Hope this helps,

 

Richard

 

Why don't you join the tables together and form a star schema in the visual relationships view, this is best practice? You coun also merge the data together in Power Query if you would rather deal with a single denormalized table.

 

 

Highlighted
abaak Frequent Visitor
Frequent Visitor

Re: From 3 tables to one

I just need the last table to get a visual we want. I am still a beginner in these kind of things so I could not get the merging of tables to work. There is probably an easy way of doing this. or another way to create the visual I want.

 

I want to create a column chart with the Cosmetic damage, privacy data and defect (and other in the real data) in the x-axis as levels. so you can see per attribute how many items have heavy damange or no damage or medium damage. same for privacy data.

 

I just have a hard time creating this.

 

richbenmintz
Advisor

Re: From 3 tables to one

Hi @abaak,

 

k, the simple solution

-> create two calculated columns in item_attribute table

Type Value = RELATED('Table1'[Name]) 
Value Name = RELATED(Table2[Value]) 

-> Use the Matrix Visualisation, place Item_id on rows, Type Value on Columns and Value Name on Values and you should see the following table

related column.PNG

 

View solution in original post

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 291 members 3,252 guests
Please welcome our newest community members: