Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
gpiero
Skilled Sharer
Skilled Sharer

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

 

pict1.PNG

 

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

 

Thanks

If I can...
1 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

 

pict3.PNG

 

 

#"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

If I can...

View solution in original post

14 REPLIES 14
v-lili6-msft
Community Support
Community Support

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

13.PNG

12.PNG

Don't enter any value in Value To Find

 

then use your formula to add a custom column

 

Result:

Before

14.PNG

After 

15.PNG

 

Best Regards,

Lin

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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

 

If I can...

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.

 

pict.PNG

 

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

 

 

If I can...

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

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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.

pict2.PNG

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

If I can...

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

 

pict3.PNG

 

 

#"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

If I can...

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

If I can...

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

v-lili6-msft
Community Support
Community Support

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

 

 

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

@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

If I can...

@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

If I can...

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

 

pict.PNG

 

Could you help me to solve the issue?

Thanks

If I can...
Stachu
Community Champion
Community Champion

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



Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

Stachu
Community Champion
Community Champion

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



Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.