Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Merge the tables

Good morning all !   I would like your help on a problem that I have been encountering for weeks! I will explain my situation to you very quickly:   In my Power Bi report, I have two tables:   ...
Need Help
  • Jimmy801's avatar
    Jimmy801
    5 years ago

    Hello Anonymous 

     

    didn't you post this or at least a very similar question already? What changed now?

     

    BR

     

    Jimmy

  • Jimmy801's avatar
    Jimmy801
    5 years ago

    Hello

     

    what about creating a material table (extracting all material code from your both table and get distinct value) and a date table, connect all three tables and then use a matrix like this

     

     

     

    If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
    Kudoes are nice too

    Have fun

    Jimmy

     

     

     

  • Jimmy801's avatar
    Jimmy801
    5 years ago

    Hello Anonymous 

     

    for the date-table is better to create i manually, and not extracting others dates. So just use function like List.Dates with 3 parameters and create a list of dates and then transform it to a table. What you can however do ist to calculate the start date form your earliest date present in the tables. Hope I was clear enough

     

    If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
    Kudoes are nice too

    Have fun

    Jimmy

Jimmy801's avatar
Jimmy801
Community Champion
5 years ago

Hello Anonymous 

 

you could then add a key-column to both of your tables where your are combining date and material number. In Power BI create a realationship between them and visual your data. Will this work out for you?

 

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Jimmy801 

I just tried your method and it doubles the lines.
don't you know another solution?

 

regards,

  • Jimmy801's avatar
    Jimmy801
    Community Champion
    5 years ago

    Hello

     

    what about creating a material table (extracting all material code from your both table and get distinct value) and a date table, connect all three tables and then use a matrix like this

     

     

     

    If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
    Kudoes are nice too

    Have fun

    Jimmy

     

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      I believe this works.

      Do you know how I have to extract the item codes in my Master Table?

      • Jimmy801's avatar
        Jimmy801
        Community Champion
        5 years ago

        Hello Anonymous 

         

        you mean material codes?

        I used this code to get the material-table. I combined both columns of material code, get distinct values and then transformed into a table

        let
    Source = List.Distinct(List.Combine({DAD[Material Code], Master[Material Code]})),
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Material Code"}})
in
    #"Renamed Columns"

        Copy paste this code to the advanced editor in a new blank query to see how the solution works.

        If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
        Kudoes are nice too

        Have fun

        Jimmy