Forum Discussion

VinceLuna's avatar
VinceLuna
Frequent Visitor
3 years ago
Solved

move text

Have a table with titles and first names combined in one field. I split the field to Name.1, Name.2, Name.3 etc. In Name.1 there are some common titles, DR., Mrs, Deacon, Pastor etc. There are about...
Need Help
  • AlienSx's avatar
    AlienSx
    3 years ago

    Hi, VinceLuna 

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bY9BC8IwDIX/yth5iHjxrBOV6VRUvMwdwhZcsWsg6xT/vWkHk4KXJM1H+t4rijiKk/iML6kn6LW0PZIBruMyGWAKtvlIv4GReiC2I8pBSb2yJ9G4XpiO3GbD9MIuQDnww93sCTvUARpMrKhVRlWDj2SEOzGBrAz6mWt6BrcrRv8OpFBT7YxfWmWbAGXgP8qhajQqG7A1TzyyIviWaau0/ojiz4vkrVyGJTyR/0TIqHHpj/d+Op3NU6dVll8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Title = _t, Name.1 = _t, Name.2 = _t, Name.3 = _t]),
    titles = List.Buffer({"DR.", "Mrs", "Rev", "Deacon", "Pastor", "Dre"}),
    tbl_list = List.Buffer(Table.ToRows(Source)),
    out = List.Transform(tbl_list, (x) => if List.Contains(titles, x{1}) then List.Skip(x) & {""} else x),
    z = Table.FromRows(out, Table.ColumnNames(Source))
in
    z
VinceLuna's avatar
VinceLuna
Frequent Visitor
3 years ago

Thanks I'll give it a shot.  Have to figure our where to insert code. 

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Hi VinceLuna ,

 

Create a blank query in Power Query editor.

And then copy and paste his code into the advanced editor under "View".

 

Query overview in Power BI Desktop - Power BI | Microsoft Learn

 

                                                                                                                                                         

Best Regards,

Stephen Tao

 

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