Forum Discussion

gpiero's avatar
gpiero
Icon for Skilled Sharer rankSkilled Sharer
8 years ago
Solved

How to merge query between a table created by modeling a a table created by Get Data

 

I have a table created by DAX codebelow

TableCompReversed = SUMMARIZECOLUMNS(CJI3[Material  ];CJI3[  Object          ];CJI3[CheckGroupMtrl];CJI3[Postg Date];FILTER(CJI3;CJI3[CheckGroupMtrl] = 0))

 

 

 

I would like to merge it with a table created by

let
    Source = Csv.Document(File.Contents("W:\ZINDICATORI.unl"),[Delimiter="|", Columns=38, Encoding=1252, QuoteStyle=QuoteStyle.None]),

 

The first table does not appear in the box below

 

 

how can I create the first file in a Query instead of a DAX espression in modeling.

 

Thanks

Need Help
  • gpiero's avatar
    gpiero
    8 years ago

    Hi v-lili6-msft

     

    I have added CheckGroupMatrl2 to manage only the case 3. Look at the formula below.

    Using or instead of and  I get the right value according to DAX code in the CheckGroupMtrl (wich is the original column I have to trasform from the calculated column although I do not have a logical explanation of that, in DAX code work well and but in M only or works well)

     

    #"Added Conditional Column1" = Table.AddColumn(#"Added Conditional Column", "CheckGroupMtrl2", 
                   each if [COOIS.FatherGroup] <> null
                          or ([#"Material  "] = null
                             or [#"Material  "] = "")

                  then 3 else 99)

     

     

    Then I replaced the formula in CheckGroupMtrl1 and as you can see it works well

     

     

     

    #"Added Conditional Column" = Table.AddColumn(#"Renamed Columns", "CheckGroupMtrl1", 
                  each if Text.Trim([#"COOIS.FatherGroup"]) <> null
                          and [#"Material  "] <> null
                          and Text.Trim([#"COOIS.FatherGroup"]) = [#"Material  "]
                  then 1
                  else if 
                          ([#"COOIS.FatherGroup"] <> null
                          or ([#"Material  "] = null
                              or [#"Material  "] = ""))
                  then 3

     

     

    At this point I believe we can consider close this issue.

     

    Thanks for your support.

    Regards

14 Replies

  • Stachu's avatar
    Stachu
    Icon for Community Champion rankCommunity Champion
    8 years ago

    in Query Editor
    1) right click on CJI3, reference it
    2) remove unnecessary columns
    3) with all columns selected rwmvoe duplicate rows
    4) filter CJI3[CheckGroupMtrl] to 0 only
    5) raname the table to TableCompReversed

    it will now appear in the merge window

  • v-lili6-msft's avatar
    v-lili6-msft
    Icon for Community Support rankCommunity Support
    8 years ago

    hi,@gpiero

       The table you create by dax code include column is  belong to the modeling process, they can't be used in the

    Edit Queries, operations in Edit Queries are belong to clear data source. So you can do these for requirement:

    STEP1:

    Duplicate the table CJI3 

    Step2:

    Filter the table CJI3 that  CJI3[checkGroupMtrl]=0

    Step3:

    Use group by function to create the table  TableComReversed

    Step4:

    Merge these two tables

     

    Best Regards,

    Lin

     

     

    • gpiero's avatar
      gpiero
      Icon for Skilled Sharer rankSkilled Sharer
      8 years ago

      Stachu, v-lili6-msft

       

      many thanks both of you.

       

      Now it is clear how to get same result in Edit Query mode.

      I'll test it next week.

       

      Thanks again

      • gpiero's avatar
        gpiero
        Icon for Skilled Sharer rankSkilled Sharer
        8 years ago

        Stachu, v-lili6-msft

         

        I tried and I confirmed it is the right solution.

         

        Unfortunatly, in the CJI3 I put some calculated column.

        So now I have to decide if replicate those column in the duplicate query or modify the original CJI3 before to duplicate it.

         

        I think to modify the original query should be the best, but it to substitute LOOKUPVALUE with a merge query.

        In case I need more support I'll send some other post

         

        Thanks

  • v-lili6-msft's avatar
    v-lili6-msft
    Icon for Community Support rankCommunity Support
    8 years ago

    hi,@gpiero

        Yes, after my research, you can do this as below:

    select the columns that you will need in the formula and replace value like below before you add the custom column

    Don't enter any value in Value To Find

     

    then use your formula to add a custom column

     

    Result:

    Before

    After 

     

    Best Regards,

    Lin

    • gpiero's avatar
      gpiero
      Icon for Skilled Sharer rankSkilled Sharer
      8 years ago

      Hi v-lili6-msft

      I realize it is a very strange case.....

       

      Replace value as per your suggestion does not work on column Material.

       

      I can replace a value with null in another column and it works. I can see null in edit mode.

      I also tried Text.Clean on Material but nothing.

       

      Regards

       

      • gpiero's avatar
        gpiero
        Icon for Skilled Sharer rankSkilled Sharer
        8 years ago

        Hi v-lili6-msft, Stachu

         

        I have decided to modify the logic into Query Editor since I was not able to achieve the value replacement suggested.

         

         

         

         

        #"Added Conditional Column" = Table.AddColumn(#"Renamed Columns", "CheckGroupMtrl1", 
                  each if Text.Trim([#"COOIS.FatherGroup"]) <> null
                          and [#"Material  "] <> null
                          and Text.Trim([#"COOIS.FatherGroup"]) = [#"Material  "]
                  then 1
                  else if 
                          Text.Trim([#"COOIS.FatherGroup"]) <> null
                          and Text.Trim([#"Material  "]) <> null
                              or [#"Material  "] <> ""
                          and [#"Material  "] <> Text.Trim([#"COOIS.FatherGroup"])
                          and Text.Trim([#"Cost Elem."]) = "M463009100"
                  then 0
                  else if
                          [#"COOIS.FatherGroup"] = null 
                          and Text.Trim([#"Material  "]) = null 
                              or Text.Trim([#"Material  "]) = ""
                  then 2
                  else if 
                          [#"COOIS.FatherGroup"] <> null
                          and [#"Material  "] = ""
                              or [#"Material  "] = null
                  then 3
                  else if
                          [#"COOIS.FatherGroup"] = null
                          and [#"Material  "] <> null
                              or [#"Material  "] = "" 
                  then 4 
                  else null)

         

        It works except for the 3rd else if.

         

         

        CheckGroupMtrl1 should be 3 not 0 (CheckGroupMaterial is the field got by teh previous DAX calculation)

         

        I  visited this link https://msdn.microsoft.com/query-bi/m/power-query-m-reference ( section Text Function)hoping to find helpful.

         

        Would you be so kind to help me to understand where is the mistake?

        I enclosed a link containing the CSV file of the table above.

         

        Regards