Need column in respective arrangement

[Sohaib]

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 are issue which need to be solve also i need to do it as "source is from folder" 

1.csv
1.csv

2.csv

I get this below result through this coding in which the new column also handles for future but data comes in new rows of same value of site column. E.g the site column value "BTL206" comes two times, one for 1.csv and second for 2.csv. It should only comes 1 times and the relevant value should be in the same row which is not in this case. Keep in mind that I need to use "source from a folder".

the M code

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

Need this below result

[Anonymous]

Sounds like you need to pay a consultant.

--Nate

[Anonymous]

Did you try my solution? It does what you are trying to achieve.

--Nate

[Omid_Motamedise · Accepted solution]

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]

I forgot to mentioning, befor pivoting the columsn, change the type of value column into number

[AlienSx]

@Sohaib before you combine your tables, unpivot all columns but "Site" (Table.UnpivotOtherColumns), then combine (Table.Combine) and finally pivot column with attributes (all your headers are in that column after unpivot operation)

[Omid_Motamedise]

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]

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]

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]

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.

[Omid_Motamedise]

You need to use the merge command instead of appending, If you have used Merge, what was the problem?

[Sohaib]

Because my source is folder not single files. Means I need to combine all the files in the folder automatically and all the data of uncommon column arrange with respect to common column

[Ahmedx]

Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

[Sohaib]

thanks for the reply but my source is folder not single file to be uploaded separately

[Anonymous]

We can all do well to remember that demoting headers is a great way to solve column name issues amongst multiple tables.

--Nate

[Anonymous]

In your Transform Sample file query, you can just click "Use Headers as First row (demote headers), then click Transpose, then click "Use First Row as Headers".  Now you have:

Now when you combine your table columnns, you'll have:

Now you can just click "Use Headers as First Row", click Transpose, then click "Use First Row as Headers", and you are done:

--Nate

[Sohaib]

I have to get data source from folder not want to individually select table in my query. Your method work when we have two tables already loaded manually in transform query. Thanks for the help and input

[jgeddes]

Here is an example of combining three tables with one similar column and then getting ride of null values.
Starting with...

and end up with...

Paste the code into the advanced editor of a blank query and you can review the steps...

let
    tableOneSource = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcjbUUQgyVNJRCgEynI3AnFgdqLgRsrgRQtwYWdxYKTYWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [tableOneColumnOne = _t, tableOneColumnTwo = _t]),
    tableTwoSource = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcjbUUQgyVNJRCjHSUXA2AnNidaDiRsjiRghxY2RxY6XYWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [tableTwoColumnOne = _t, tableTwoColumnTwo = _t]),
    tableThreeSource = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcjbUUQgyVNJRCjHWUXA2AnNidaDiRsjiRghxY2RxY6XYWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [tableThreeColumnOne = _t, tableThreeColumnTwo = _t]),
    combineNoRename = 
    Table.Combine(
        {tableOneSource, tableTwoSource, tableThreeSource}
    ),
    renameTableOneColumnOne = 
    Table.RenameColumns(tableOneSource, {"tableOneColumnOne", "ColumnOne"}),
    renameTableTwoColumnOne = 
    Table.RenameColumns(tableTwoSource, {"tableTwoColumnOne", "ColumnOne"}),
    renameTableThreeColumnOne =
    Table.RenameColumns(tableThreeSource, {"tableThreeColumnOne", "ColumnOne"}),
    combineColumnOneRename = 
    Table.Combine({renameTableOneColumnOne, renameTableTwoColumnOne, renameTableThreeColumnOne}),
    groupByColumnOne = 
    Table.Group(
        combineColumnOneRename, 
        {"ColumnOne"}, 
        {
            {"allRows", each _, type table [ColumnOne=nullable text, tableOneColumnTwo=nullable text, tableTwoColumnTwo=nullable text, tableThreeColumnTwo=nullable text]}
        }
    ),
    unpivotNonGroupedColumns = 
    Table.TransformColumns(
        groupByColumnOne, 
        {
            {"allRows", each Table.UnpivotOtherColumns(_, {"ColumnOne"}, "name", "value")}
        }
    ),
    transposeColumns = 
    Table.TransformColumns(
        unpivotNonGroupedColumns, 
        {
            {"allRows", each Table.PromoteHeaders(Table.Transpose(Table.SelectColumns(_, {"name", "value"})))}
        }
    ),
    expandInnerTables = 
    Table.ExpandTableColumn(
        transposeColumns, 
        "allRows", 
        {"tableOneColumnTwo", "tableTwoColumnTwo", "tableThreeColumnTwo"}
    )
in
    expandInnerTables

 Hope this gets you pointed in the right direction.

[Sohaib]

thanks for the reply and this provided example but there should be other way around as it is not applicable when we are dealing with higher number of columns for heavy data sets. I am looking forward for another solution.