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

[gpiero]

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

[v-lili6-msft]

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]

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]

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

[v-lili6-msft]

hi,@gpiero

   After my research, you may try  to adjust your logical order in your formula

try to this formula:

 #"Added Conditional Column" = Table.AddColumn(#"Changed Type1", "Custom", each if Text.Trim([#"COOIS.FatherGroup"]) <> null
                          and [#"Material  "] <> null
                          and Text.Trim([#"COOIS.FatherGroup"]) = [#"Material  "]
                  then 1
else if 
                          [#"COOIS.FatherGroup"] <> null
                          and ([#"Material  "] = ""
                              or [#"Material  "] = null)
                  then 3
                  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  "] <> null
                              or [#"Material  "] = "" )
                  then 4 
                  else null)

Best Regards,

Lin

[gpiero]

Hi @v-lili6-msft

many thanks for you support. What is clear to me now is the right priority with or operator  due the parenthesis added.

Applying your logical order I've got as below. Now only some rows contains 0 in the right colmun, the most part are blank.

In my formula version all rows at right containes 0

I also tried to add the 2nd or in your formula, but nothing changed.

else if 
                          [#"COOIS.FatherGroup"] <> null
                          and ([#"Material  "] = null
                              or [#"Material  "] = ""
                              or Text.Length([#"Material  "]) = 0 )
                  then 3

So I reversed it at your original version.

Regards

[gpiero · Accepted solution]

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

[gpiero]

Hi @v-lili6-msft

I solved even the issue related to Replace Value

It did not work because the field Material exported from ERP contained 10 white space and it wasn't neither NULL nor "".

Then even or/and operator was replaced as per initial DAX code.

Thanks again

Regards

[Willmore]

I've found so many useful information in this post!
thanks!

[v-lili6-msft]

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]

@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]

@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

[gpiero]

@v-lili6-msft, @Stachu

I am trying to trasform a calculated column by DAX in M language into Query Editor.

CheckGroupMtrl =
IF (
    CJI3[COOIS.FatherGroup] <> BLANK ()
        && CJI3[Material  ] <> BLANK ()
        && CJI3[COOIS.FatherGroup] = CJI3[Material  ];
    1;
    IF (
        CJI3[Material  ] <> BLANK ()
            && CJI3[COOIS.FatherGroup] <> BLANK ()
            && CJI3[Material  ] <> CJI3[COOIS.FatherGroup]
            && CJI3[Cost Elem.] = "M463009100";
        0;
        IF (
            CJI3[COOIS.FatherGroup] = BLANK ()
                && CJI3[Material  ] = BLANK ();
            2;
            IF (
                CJI3[COOIS.FatherGroup] <> BLANK ()
                    && CJI3[Material  ] = BLANK ();
                3;
                IF (
                    CJI3[COOIS.FatherGroup] = BLANK ()
                        && CJI3[Material  ] <> BLANK ();
                    4;
                    BLANK ()
                )
            )
        )
    )
)

the formula above is OK

Appling the what you see below I did not get any error in the Query Editor

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

But I've got a differen result.

Could you help me to solve the issue?

Thanks

[Stachu]

I think that BLANK() in DAX covers both M equivalent of null and "", that may be the reason for inconsistency
https://msdn.microsoft.com/en-us/query-bi/dax/blank-function-dax

[Stachu]

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