Duplicating some data by adding a table and union them

[MiKeZZa]

Hi all,

I want to duplicate/manipulate a imported table. The table has many columns, but to keep it simple lets say 3 columns...

yearmonth   value    origin

201612          10         real data

201612          12         real data

201701          8           real data

201701          18         real data

201701          6           real data

201702          5           real data

201701          18         real data

201701          1           real data

201702          6           real data

201702          4           real data

201703          8           prognose

201703          6           prognose

Now I need to create a dataset with all values from above, with 201702 data duplicated with 'origin' prognose. This to create something like this:

I think I'm almost there, but I think it can be done easier and I can't do the last part:

1. Create a smaller dataset ; make a filter on origin so that 201702 is the max value in that dataset:
   

   FILTER (ALL ('table'), 'table'[origin] = "Real data")

2. Take only the data from the highest month
   

   VAR MaxDate = (MAX ( 'table 2'[yearmonth] ))
   RETURN 
   	FILTER ('table 2', 'table 2'[yearmonth] = MaxDate)

3. Create a table from the dataset at point 2 and update column origin to 'forecast'. Because I don't know how to do this (can this be done???) I first delete the columns and add the column again:
   

   SUMMARIZE('table 3', 'table 3'[yearmonth],'table 3'[value])

   ADDCOLUMNS('table 4', "origin", "forecast")

4. Now combine the original dataset with this forecast records
   

   union('table 5','table')

The 2 problems I have now are:

1. Data is strange; it seems that columns are not at the same order; so I get a dataset with wrong values in the columns
2. I think creating 5 datasets for duplicating some data and do 1 update is a little overkill. Can this be done easier at all?

[MarcelBeug]

It can simply be done with Power Query.

The first 3 steps of the code below is the source data (from Excel in this example).

This video illustrates how the other steps look like (recorded after the code was created).

let
    Source = Excel.Workbook(File.Contents("C:\Users\Marcel\Documents\Forum bijdragen\Power BI Community\Duplicating some data.xlsx"), null, true),
    Tabel1_Table = Source{[Item="Tabel1",Kind="Table"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(Tabel1_Table,{{"yearmonth", Int64.Type}, {"value", Int64.Type}, {"origin", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each if [yearmonth] = 201702 then {1..2} else {1}),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"),
    #"Added Custom1" = Table.AddColumn(#"Expanded Custom", "New origin", each if [Custom] = 1 then [origin] else "prognose"),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"origin", "Custom"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"New origin", "Origin"}}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Renamed Columns",{{"Origin", type text}})
in
    #"Changed Type1"

[MiKeZZa]

Wow. That's completely new to me.... I was so happy with my DAX-progress and now comes Power Query 

I'll give it a try if there is no way to do it in DAX easy.

[MiKeZZa]

For now I'm a little in a hurry and can't find out how to get this done in Power Query.

Can somebody tell me how to combine 2 tables? I've done that with union but I get strange values in the columns. I think the cause is maybe the order of the columns in my table. But the strange thing is that I order them with:

SUMMARIZE('table 3', 'table 3'[yearmonth],'table 3'[value])

But this is not the order that I see in Power BI Data tab....

Can this be the cause and how can I order the fields?

[Phil_Seamark · Accepted solution]

Hi @MiKeZZa,

How close is this?  Create a calculated table using the following.....

New DAX table = 
VAR Tb1 = FILTER('Table','Table'[origin]="Real data")
VAR MaxDate = CALCULATE(MAX('Table'[yearmonth]),'Table'[origin]="Real data")
VAR Tb2 = SELECTCOLUMNS(
            FILTER('Table','Table'[origin]="Real data" && 'Table'[yearmonth] =MaxDate),
            "yearmonth" , [yearmonth] ,
            "value" , [value],
            "origin" , "forecast")
Return UNION(Tb1,Tb2)

[MiKeZZa]

Yes this is great!!! Simple and effective!

I've had some issues with it; it stopped working my Power BI Desktop a few times, but after stopping with pasting the code into it and making it myself it worked great.

I've changed the last rule of code to this:

Return union('Table',tb2)

because of that I want the whole dataset, with the duplicated month.