Exam 70-779 topic 1 question 66 discussion

Actual exam question from Microsoft's 70-779

Question #: 66
Topic #: 1

DRAG DROP -
You have a workbook query that retrieves data from a table named Users. Users contains a column named PhoneNumber. The following is a sample of the data in PhoneNumber.
514 555 0160
1 (11) 500 555-0122
128 555-0148
819 555-0186
1-996-555-0192
+1 138-555-0156
556-555-0192
You need to create a custom column that contains the data in PhoneNumber in the format of 999-999-9999. The following is a sample of the desired data.
514-555-0160
500-555-0122
128-555-0148
819-555-0186
996-555-0192
138-555-0156
556-555-0192
How should you complete the Query Editor formula? To answer, drag the appropriate values to the correct targets. Each value may be used once, more than once, or not at all. You may need to drag the split bar between panes or scroll to view content.
NOTE: Each correct selection is worth one point.
Select and Place:

Show Suggested Answer

Suggested Answer:

Reference:
https://docs.microsoft.com/en-us/powerquery-m/text-replacerange https://docs.microsoft.com/en-us/powerquery-m/text-end

by imran at Oct. 10, 2019, 4:44 a.m.

Comments

Submit Cancel

imran

Highly Voted 5 years, 8 months ago

text.replace is correct

upvoted 25 times

Tr4ckz

5 years, 6 months ago

confirmed, Text.ReplaceRange uses 4 inputs, not 3 as here in the example --> Text.Replace(Text-End([...]),[...],[...])

upvoted 16 times

...

ks70393

5 years, 2 months ago

https://docs.microsoft.com/en-us/powerquery-m/text-replace

upvoted 1 times

...

CDL

Most Recent 4 years, 8 months ago

Text.Replace + Text.End {12 character} Replace (space) with (dash)}.

upvoted 4 times

...

CDL

4 years, 8 months ago

I did it, not using either code but below: let Source = Excel.CurrentWorkbook(){[Name="PhoneNum"]}[Content], #"Changed Type" = Table.TransformColumnTypes(Source,{{"PhoneNumber", type text}}), #"Duplicated Column" = Table.DuplicateColumn(#"Changed Type", "PhoneNumber", "PhoneNumber - Copy"), #"Calculated Text Length" = Table.TransformColumns(#"Duplicated Column",{{"PhoneNumber - Copy", Text.Length, Int64.Type}}), #"Duplicated Column1" = Table.DuplicateColumn(#"Calculated Text Length", "PhoneNumber", "PhoneNumber - Copy.1"), #"Renamed Columns" = Table.RenameColumns(#"Duplicated Column1",{{"PhoneNumber - Copy", "Length"}}), #"Extracted Last Characters" = Table.TransformColumns(#"Renamed Columns", {{"PhoneNumber - Copy.1", each Text.End(_, 12), type text}}), #"Replaced Value" = Table.ReplaceValue(#"Extracted Last Characters"," ","-",Replacer.ReplaceText,{"PhoneNumber - Copy.1"}) in #"Replaced Value"

upvoted 1 times

...

Rafa

4 years, 10 months ago

Text.Replace is the answer.

upvoted 1 times

PowerBIconsultant12

4 years, 8 months ago

Yes that is right. This question is also in the 70-778 PowerBI exam

upvoted 1 times

...

Rymen

5 years, 2 months ago

text.replace is correct, tested text.replacerange will give u error but text.replace will give u the desire result

upvoted 2 times

...