Combine columns from different datasets

[karelstoel]

Hi all,

I have a question about two columns from other dataset that I would like to combine. The datasets are older(1) and newer (2) data, so I can't combine them per rule. See below (pricture) the first two columns that I would like to combine and the third and fourth column that I would like to combine.

Thanks and have a nice weekend, hope that somebody can help me.

Kinds regards, Karel

[parry2k · Accepted solution]

@karelstoel I guess these are two tables with different column names, there are many ways to do this. You can use append queries in Power Query and for this, you need to make sure the column name are the same, rename the columns and then use append and that will do it.

✨ Follow us on LinkedIn

Check my latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[smpa01 · Accepted solution]

@karelstoel  if you have a table like this called new

| date       | value |
|------------|-------|
| 2021-01-02 | 1000  |
| 2021-01-02 | 2000  |
| 2021-01-03 | 3000  |

  and a table like this called old

| date       | value |
|------------|-------|
| 2021-01-01 | 100   |
| 2021-01-01 | 200   |
| 2021-01-02 | 400   |

 and if you choose to create a new table through DAX that would generate all the distinct date and combined values, this is how you can do it

Table =
VAR _0 =
    DISTINCT ( UNION ( VALUES ( new[date] ), VALUES ( old[date] ) ) )
VAR _1 =
    ADDCOLUMNS (
        _0,
        "val",
            SUMX ( FILTER ( new, new[date] = EARLIER ( [date] ) ), new[value] )
                + SUMX ( FILTER ( old, old[date] = EARLIER ( [date] ) ), old[value] )
    )
RETURN
    _1

[karelstoel]

Great guys, this will help me! I am gonna check the solutions.

Thanks and kind regards, Karel

[Anonymous]

Hi @karelstoel ，

Whether your problem has been resolved? If yes, could you please mark the helpful post as Answered? It will help the others in the community find the solution easily if they face the same problem with yours. Thank you.

Best Regards

[PhilipTreacy · Accepted solution]

Hi @karelstoel 

Please see this PBIX file for a solution

If your columns have different names (as you've shown) then you could use Power Query to merge these columns using code like this

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjJUcEtNKipNLKpUMDIwNFfSUYIgEwtLPVMDpVgdoBoDHGoM9AwgKiBcYxMDCxM9SyMQDypqaKHgVZoD1mYJ4hpZ6hmbIcsbKziWppcWl8BVmFiagkwFKYkFAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Factuur.Factuurdatum = _t, Order_Date = _t, Service_Entry_Revenue_WIP = _t, Gefactureerd = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Factuur.Factuurdatum", type date}, {"Order_Date", type date}, {"Service_Entry_Revenue_WIP", type number}, {"Gefactureerd", type number}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "DateCol", each if [Factuur.Factuurdatum] is null then [Order_Date] else [Factuur.Factuurdatum]),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "NumberCol", each if [Service_Entry_Revenue_WIP] is null then [Gefactureerd] else [Service_Entry_Revenue_WIP]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom1",{{"DateCol", type date}, {"NumberCol", type number}})
in
    #"Changed Type1"

Basically, it checks (for example) both date columns and takes the value from whatever column is not blank.  Then does the same for the numeric columns. 

Giving this

Note that you have a row where there is no date in either of the date columns in which case you'll get no date in the merged column too.

Regards

Phil

[smpa01 · Accepted solution]

@karelstoel  if you have a table like this called new

| date       | value |
|------------|-------|
| 2021-01-02 | 1000  |
| 2021-01-02 | 2000  |
| 2021-01-03 | 3000  |

  and a table like this called old

| date       | value |
|------------|-------|
| 2021-01-01 | 100   |
| 2021-01-01 | 200   |
| 2021-01-02 | 400   |

 and if you choose to create a new table through DAX that would generate all the distinct date and combined values, this is how you can do it

Table =
VAR _0 =
    DISTINCT ( UNION ( VALUES ( new[date] ), VALUES ( old[date] ) ) )
VAR _1 =
    ADDCOLUMNS (
        _0,
        "val",
            SUMX ( FILTER ( new, new[date] = EARLIER ( [date] ) ), new[value] )
                + SUMX ( FILTER ( old, old[date] = EARLIER ( [date] ) ), old[value] )
    )
RETURN
    _1

[parry2k · Accepted solution]

@karelstoel I guess these are two tables with different column names, there are many ways to do this. You can use append queries in Power Query and for this, you need to make sure the column name are the same, rename the columns and then use append and that will do it.

✨ Follow us on LinkedIn

Check my latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡