Intellipaat Back

Explore Courses Blog Tutorials Interview Questions
0 votes
3 views
in BI by (11.1k points)

I am having a column with series of elements an excel sheet called list with series of letters and an excel sheet called column to add to each letter of the list

Here is the sheet of the list which I have created

   |---------------------|  
  |       Name          |  
  |---------------------|
  |         A           |    
  |---------------------|
  |         B           |     
  |---------------------|
  |         C           |    
  |---------------------|
  |        D            |    

and here is the column to add files

                        |---------------------|-----------------| -------------|-----------------|   
                        |      Pro1           |     Pro2        |   Pro3       |      Pro4       | 
                        |---------------------|-----------------| -------------|-----------------|  
                        |         56          |     dgdt        |  gdro1       |     gggggro1    | 
                        |---------------------|-----------------| -------------|-----------------|  
                        |        56           |     dgdt        |  gdro1       |     gggggro1    | 
                        |---------------------|-----------------| -------------|-----------------|  
                        |        4            |    dgdt         |  gdro1       |     gggggro1    | 
                        |---------------------|-----------------| -------------|-----------------|  
                        |     4               |    4                  4                  4

for each element i am trying add 4 columns from other files,The following would be result

 I have tried with the following codes and they are not working

#"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"       Name          ", type text}}),
#"Transposed Table" = Table.Transpose(#"Changed Type1"),
#"repeat Table" =   Table.Repeat(#"Transposed Table",4),
#"Added Index" = Table.AddIndexColumn(#"repeat Table", "Index", 1, 1)
in
#"Added Index"
let
    Source = Table.NestedJoin(#"columns to add", {"Index"}, list, {"Index"}, "list", JoinKind.FullOuter),
    #"Expanded list" = Table.ExpandTableColumn(Source, "list", {"Column1", "Column2", "Column3", "Column4", "Index"}, {"list.Column1", "list.Column2", "list.Column3", "list.Column4", "list.Index"})
in 

    #"Expanded list" 

How can I do this? 

1 Answer

0 votes
by (22.5k points)

Use the following measure

let Part1 = Table.AddColumn(Table1, "Custom", each 1),
Part2 = Table.AddColumn(Table2, "Custom", each 1),
#"Merged Queries" = Table.NestedJoin(Part1,{"Custom"},Part2,{"Custom"},"Part2",JoinKind.FullOuter),
#"Expanded Part2" = Table.ExpandTableColumn(#"Merged Queries", "Part2", {"Pro1", "Pro2", "Pro3", "Pro4"}, {"Pro1", "Pro2", "Pro3", "Pro4"}),
#"Removed Columns" = Table.RemoveColumns(#"Expanded Part2",{"Custom"})
in #"Removed Columns"

Want to learn more about Power Bi, Refer: Power Bi Training   

Related questions

31k questions

32.8k answers

501 comments

693 users

Browse Categories

...