Merge tables with differents colums names

[Anonymous]

Hi

I have two sources:

1 - with A, B, C as columns labels

2 - with A, B, D as columns labels

I want as output: A, B, (C or D)

The C or D is conditional to the Column B

Both of my sources are pushed from my CRM on a periodicaly base in a XLSX format. And I use as source a folder.

How should I proceed? Create 2 sources?

Thanks

[Icey · Accepted solution]

Hi @Anonymous ,

How about this:

let
    Source = Folder.Files("C:\xxx\xxx\xxx\xxx"),
    #"Added Custom" = Table.AddColumn(Source, "Workbook", each Excel.Workbook(File.Contents([Folder Path]&"\"&[Name]), null, true)),
    #"Expanded Workbook" = Table.ExpandTableColumn(#"Added Custom", "Workbook", {"Name", "Data", "Item", "Kind", "Hidden"}, {"Name.1", "Data", "Item", "Kind", "Hidden"}),
    #"Filtered Rows" = Table.SelectRows(#"Expanded Workbook", each ([Kind] = "Table")),
    #"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows",{"Name", "Folder Path", "Data"}),
    #"Expanded Data" = Table.ExpandTableColumn(#"Removed Other Columns", "Data", {"A", "B", "C", "D"}, {"A", "B", "C", "D"}),
    #"Added Custom1" = Table.AddColumn(#"Expanded Data", "C/D", each if [B] = "B1" then [C] else [D]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"C", "D"}),
    #"Filtered Rows1" = Table.SelectRows(#"Removed Columns", each ([#"C/D"] <> null))
in
    #"Filtered Rows1"

BTW, .pbix file attached.

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@Anonymous , First of all append these two tables. You will columns A,B,C,D

https://radacad.com/append-vs-merge-in-power-bi-and-power-query

Then you can write a condition column in Power Query or DAX

Example

M

E= If [B] = "ABC" then [C] else [D]

DAX

E = if([B]= "ABC" ,[C] ,[D])

[Anonymous]

Thanks for you answer.

I wasn't cleat abour my first issue: As I'm using a folder where all my XLXS are stored (2 types of file generated each month) I'm wondering how dans this kind of datasource (folderà i can import files with different structure.

[amitchandak]

@Anonymous , You can import files from a folder, they should be same structure

example

https://powerbi.microsoft.com/en-us/blog/combining-excel-files-hosted-on-a-sharepoint-folder/

https://insightsoftware.com/blog/power-bi-load-data-from-folder/

For different structure from the same folder might have code in M