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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Nooby
Helper I
Helper I

Power Query: obtención de valores de columna de otra columna

Hola a todos,

Puedo hacer esto en sql muy fácil, pero tengo que hacerlo en el editor de consultas power bi power con M.

(no DAX)

Hay una columna con los valores:

TABLA1. CODE_COLUMN

XXYYZZTT
PPRRQQSS

Y hay otra columna en otra tabla

TABLA2. DIRTY_DATA

123123XXYYZZTT123123
XXYYZZTT44223xxxxxfdwe
dadfewrerXXYYZZTT987
asdf324aerq
daf33421adf
1PPRRQQSSr2d2

Tengo que buscar los primeros valores de columna uno por uno y cuando encuentro el valor, tengo que extraer el primer valor antes que él, sí mismo y siguiendo 3 caracteres con él.

Las nuevas 3 columnas serían:

TABLA2. FETCH1TABLA2. FETCH2TABLA2. FETCH3
3XXYYZZTT123
tXXYYZZTT442
rXXYYZZTT987
NullNullNull
NullNullNull
1PPRRQQSSR2d

No puedo hacerlo con botones estándar:

NOT STANDARD.png

1 ACCEPTED SOLUTION

Hola

He dividido el proceso en (esperemos) pasos digeribles (por favor, compruebe también el archivo adjunto):

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("NYo7DoAgEETvQm3DLol6C34FQihIFnqxwOOLKJPXzMwLgXHAjnPH4b21X+uwuAQ2VyEA8H5TqOVxUaKSW811Ovu2jiNdVBBEyvX8xYIogHd/dC6l1koZU4GAxfgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
    ListOfFoundCodes = Table.AddColumn(#"Changed Type", "PositionOfText", each List.Transform(Table1_Code_Column[Column1], (x) => Text.PositionOf([Column1], x))),
    IndexOfCode = Table.AddColumn(ListOfFoundCodes, "IndexFoundCode", each List.PositionOfAny([PositionOfText], {0..1000})),
    PositionWithinDirtyData = Table.AddColumn(IndexOfCode, "PositionOfTextWhereFound", each List.Max([PositionOfText])),
    Fetch1 = Table.AddColumn(PositionWithinDirtyData, "Fetch1", each try Text.Range([Column1],[PositionOfTextWhereFound]-1, 1) otherwise null),
    Fetch2 = Table.AddColumn(Fetch1, "Fetch2", each if [IndexFoundCode] = -1 then null  else Table1_Code_Column{[IndexFoundCode]}[Column1]),
    Fetch3 = Table.AddColumn(Fetch2, "Fetch3", each if [IndexFoundCode] = -1 then null else Text.Range([Column1],[PositionOfTextWhereFound] + Text.Length([Fetch2]),3))
in
    Fetch3

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

View solution in original post

3 REPLIES 3
amitchandak
Super User
Super User

@ImkeF , puede ayudar

Hola

He dividido el proceso en (esperemos) pasos digeribles (por favor, compruebe también el archivo adjunto):

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("NYo7DoAgEETvQm3DLol6C34FQihIFnqxwOOLKJPXzMwLgXHAjnPH4b21X+uwuAQ2VyEA8H5TqOVxUaKSW811Ovu2jiNdVBBEyvX8xYIogHd/dC6l1koZU4GAxfgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
    ListOfFoundCodes = Table.AddColumn(#"Changed Type", "PositionOfText", each List.Transform(Table1_Code_Column[Column1], (x) => Text.PositionOf([Column1], x))),
    IndexOfCode = Table.AddColumn(ListOfFoundCodes, "IndexFoundCode", each List.PositionOfAny([PositionOfText], {0..1000})),
    PositionWithinDirtyData = Table.AddColumn(IndexOfCode, "PositionOfTextWhereFound", each List.Max([PositionOfText])),
    Fetch1 = Table.AddColumn(PositionWithinDirtyData, "Fetch1", each try Text.Range([Column1],[PositionOfTextWhereFound]-1, 1) otherwise null),
    Fetch2 = Table.AddColumn(Fetch1, "Fetch2", each if [IndexFoundCode] = -1 then null  else Table1_Code_Column{[IndexFoundCode]}[Column1]),
    Fetch3 = Table.AddColumn(Fetch2, "Fetch3", each if [IndexFoundCode] = -1 then null else Text.Range([Column1],[PositionOfTextWhereFound] + Text.Length([Fetch2]),3))
in
    Fetch3

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

Queridos @ImkeF ,

funciona como un encanto, muchas gracias.

Veo que mi conocimiento sql no ayuda en absoluto, parece tan complicado que no pude resolverlo por mi cuenta.

Hago todo en sql e intento familiarizarme con Power BI últimamente. ¿Qué fuente ofrecerías para poder escribir este código tuyo, porque los que estudié no ayudaron en absoluto?

y gracias @amitchandak!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors