Forum Discussion
Duplicating some data by adding a table and union them
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)
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"
Wow. That's completely new to me.... I was so happy with my DAX-progress and now comes Power Query :smileyvery-happy:
I'll give it a try if there is no way to do it in DAX easy.
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?
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)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.