Forum Discussion

MiKeZZa's avatar
MiKeZZa
Post Patron
9 years ago
Solved

Duplicating 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 d...
Need Help
  • Phil_Seamark's avatar
    Phil_Seamark
    9 years ago

    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's avatar
MiKeZZa
Post Patron
9 years ago

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.

MiKeZZa's avatar
MiKeZZa
Post Patron
9 years ago

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's avatar
    Phil_Seamark
    Microsoft Employee
    9 years ago

    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's avatar
      MiKeZZa
      Post Patron
      9 years ago

      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.