Forum Discussion

Sohaib's avatar
Sohaib
Helper II
2 years ago
Solved

Need column in respective arrangement

I have 2 tables data table 1 name is "1.csv" and table 2 name is "2.csv". Both table have common column named "Site" and have same value/text but when i combined both through append and merge there a...
Need Help
  • Omid_Motamedise's avatar
    Omid_Motamedise
    2 years ago

    As the number of tables are not clear, i recomend you follwo this step.
    fits append the tables to reach the result like the next image

     

     

     

     

     

    right click on column site and use unpivot other columns to reach the next result

     

     

     

    then select Attribute column and go to transform tab and pick Pivot column command.
    like the below image pic value for value column.

     

    hit ok to reach your result as below.

     

     



    I hope this help you, if you have any other question, pls do not hesitate and ask

Omid_Motamedise's avatar
Omid_Motamedise
Super User
2 years ago

Use this formula if you have just two tables, if you have more lets me know to develope it 



let
Source = Folder.Files("D:\Random working\append and combine column working"),
Custom1 = Table.TransformColumns(
Source, {"Content", each Table.PromoteHeaders(Csv.Document(_))} ),
Content = Table.Join(Custom1[Content]{0},"Site",Custom1[Content]{1},"Site",JoinKind.Inner)
in
Content

Sohaib's avatar
Sohaib
Helper II
2 years ago

Thanks for the code. Actually I want to make it dynamic so whenever we have new table in folder they automatically combine and make it to the relevant column

  • Omid_Motamedise's avatar
    Omid_Motamedise
    Super User
    2 years ago

    So use this code please check this video about the loop in Power Query.
    https://youtu.be/G8PRbWuDcmQ?si=LpbIPPQ3MWAJQO7b

    let
    Source = Folder.Files("D:\Random working\append and combine column working"),
    Custom1 = Table.TransformColumns(
    Source, {"Content", each Table.PromoteHeaders(Csv.Document(_))} ),
    Content =List.Accumulate(List.Skip(List.Posistions(Source[Content])), Custom1[Content]{0},(a,b)=> Table.Join(a,"Site",Custom1[Content]{b},"Site",JoinKind.Inner))
    in
    Content

    • Sohaib's avatar
      Sohaib
      Helper II
      2 years ago

      It is working but has some issue like the value "BTL207" of common column which is "site" is repeaing again which is kind of weird.