Forum Discussion
Post PatronDuplicating some data by adding a table and union them
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:
- 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") - Take only the data from the highest month
VAR MaxDate = (MAX ( 'table 2'[yearmonth] )) RETURN FILTER ('table 2', 'table 2'[yearmonth] = MaxDate) - 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") - Now combine the original dataset with this forecast records
union('table 5','table')
The 2 problems I have now are:
- Data is strange; it seems that columns are not at the same order; so I get a dataset with wrong values in the columns
- I think creating 5 datasets for duplicating some data and do 1 update is a little overkill. Can this be done easier at all?
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)
5 Replies
- MarcelBeug
Community Champion
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"
MiKeZZaWow. 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.
- 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?
