Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
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
Solved! Go to Solution.
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
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
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
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
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
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
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
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
I've found so many useful information in this post!
thanks!
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
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
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
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
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
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
User | Count |
---|---|
122 | |
69 | |
67 | |
58 | |
52 |
User | Count |
---|---|
183 | |
92 | |
67 | |
62 | |
52 |