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
devssolution
Frequent Visitor

Importing Data form Text

How can I import data from Text file. My text file formate as below.

 

 

      ALBERTA ENERGY REGULATOR                                        
                                                                                                        
                                   WELL LICENCES ISSUED  DAILY LIST                                     
                                                                                                        
                                                                                                        

    DATE: 01 September 2016                                                                             
                                                                                                        

    --------------------------------------------------------------------------------------------        
    WELL NAME               LICENCE NUMBER         MINERAL RIGHTS       GROUND ELEVATION                
    UNIQUE IDENTIFIER       SURFACE CO-ORDINATES   BOARD FIELD CENTRE   PROJECTED DEPTH                 
    LAHEE CLASSIFICATION    FIELD                                       TERMINATING ZONE                
    DRILLING OPERATION      WELL PURPOSE           WELL  TYPE           SUBSTANCE                       
    LICENSEE                                                            SURFACE LOCATION                
    --------------------------------------------------------------------------------------------        

    BLZ DD RAINBOW 4-34-108-9            0480273   ALBERTA CROWN        494.5M                          
    100/04-34-108-09W6/00  N  127.7M  E  499.4M    HIGH LEVEL           1960.0M                         
    DEV (NC)                             RAINBOW                        MUSKEG FM                       
    DIRECTIONAL                          NEW       PRODUCTION           CRUDE OIL                       
    BLZ ENERGY LTD.                                                     03-34-108-09W6                  
                                                                                                        
    TOURMALINE HZ VALHALLA 11-11-78-8    0480274   ALBERTA CROWN        751.9M                          
    100/11-11-078-08W6/00  N  705.6M  E  417.6M    GRANDE PRAIRIE       3400.0M                         
    DEV (C)                              VALHALLA                       CHARLIE LAKE FM                 
    HORIZONTAL                           NEW       PRODUCTION           CRUDE OIL                       
    TOURMALINE OIL CORP.                                                06-10-078-08W6                  
                                                                                                        
    CARDINAL ALDSON 14-15-14-9           0480275   ALBERTA CROWN        763.2M                          
    102/14-15-014-09W4/00  S   92.3M  W  271.1M    MEDICINE HAT         2570.0M                         
    DEV (NC)                             ALDERSON                       LOWER MANNVILLE FM              
    HORIZONTAL                           NEW       PRODUCTION           CRUDE OIL                       
    CARDINAL ENERGY LTD.                                                16-10-014-09W4                  
                                                                                                        
    PARA HZ KARR 3-7-65-5                0480276   ALBERTA CROWN        915.8M                          
    100/03-07-065-05W6/00  N   30.9M  E  211.9M    GRANDE PRAIRIE       6159.0M                         
    DEV (C)                              KARR                           MONTNEY FM                      
    HORIZONTAL                           NEW       PRODUCTION           GAS                             
    PARAMOUNT RESOURCES LTD.                                            04-19-065-05W6                  
                                                                                                        
    CNRL HZ ELM 13-7-70-7                0480277   ALBERTA CROWN        706.5M                          
    100/13-07-070-07W6/00  N  737.9M  E  381.0M    GRANDE PRAIRIE       5000.0M                         
    OUT (C)                              ELMWORTH                       MONTNEY FM                      
    HORIZONTAL                           NEW       PRODUCTION           GAS                             
    CANADIAN NATURAL RESOURCES LIMITED                                  05-06-070-07W6                  
                                                                                                        
    CVE 6D-17 BANTRY 5-17-20-14          0480278   FREEHOLD             755.9M                          
    102/05-17-020-14W4/00  N  756.1M  E  715.5M    MIDNAPORE            1271.0M                         
    DEV (NC)                             BANTRY                         LOWER MANNVILLE FM              
    DIRECTIONAL                          NEW       PRODUCTION           CRUDE OIL                       
    CENOVUS ENERGY INC.                                                 06-17-020-14W4                  
                                                                                                        
    LONG RUN HZ REDWATER 16-6-58-22      0480279   ALBERTA CROWN        657.5M                          
    100/16-06-058-22W4/00  N  367.1M  E   40.0M    ST. ALBERT           1600.0M                         
    DEV (NC)                             REDWATER                       VIKING FM                       
    HORIZONTAL                           NEW       PRODUCTION           CRUDE OIL                       
    LONG RUN EXPLORATION LTD.                                           04-05-058-22W4                  
                                                                                                        
    LONG RUN HZ 102 REDWATER 16-6-58-22  0480280   ALBERTA CROWN        657.5M                          
    102/16-06-058-22W4/00  N  382.1M  E   40.0M    ST. ALBERT           1600.0M                         
    DEV (NC)                             REDWATER                       VIKING FM                       
    HORIZONTAL                           NEW       PRODUCTION           CRUDE OIL                       
    LONG RUN EXPLORATION LTD.                                           04-05-058-22W4                  
                                                                                                        
    CVE FCCL W26P01 FISHER 2-21-70-5     0480281   ALBERTA CROWN        668.6M                          
    102/02-21-070-05W4/00  S  732.6M  W  664.1M    BONNYVILLE           1890.0M                         
    DEV (NC)                             FISHER                         MCMURRAY FM                     
    HORIZONTAL                           NEW       PRODUCTION (SCHEME)  CRUDE BITUMEN                   
    CENOVUS FCCL LTD.                                                   10-22-070-05W4                  
                                                                                                        
    VEI HZ FERRIER 15-20-43-10           0480282   ALBERTA CROWN        1058.6M                         
    100/15-20-043-10W5/00  N  109.0M  E  167.7M    RED DEER             4023.0M                         
    DEV (NC)                             FERRIER                        NOTIKEWIN MBR                   
    HORIZONTAL                           NEW       PRODUCTION           GAS                             
    VERMILION ENERGY INC.                                               04-20-043-10W5                  
                                                                                                        
    AMENDMENTS OF WELL LICENCES
    ---------------------------

    WELL NAME AND U.I.D.                LICENCE NO.  AMENDED TO READ
    --------------------                -----------  ---------------

    ARC 102 HZ PEMBINA 14-32-49-8         0470480    UWI:                                               
    02/14-32-049-08W5/0                              02/13-32-049-08W5/0                                

                                                     WELL NAME:                                         
                                                     ARC HZ 102 PEMBINA 13-32-49-8                      

    ARC 103 HZ PEMBINA 14-32-49-8         0470481    WELL NAME:                                         
    103/14-32-049-08W5/00                            ARC HZ 102 PEMBINA 14-32-                          

    7GEN HZ 102 KAKWA 4-29-63-4           0475271    PROJECTED DEPTH:                                   
    102/04-29-063-04W6/00                            6300.00                                            

                                                     DRILL CUTTINGS:                                    
                                                                                                        

    WELL LICENCES CANCELLED
    -----------------------

    WELL NAME AND U.I.D.                LICENCE NO.                                                     
    --------------------                -----------                                                     

    SCL HZ SUNDANCE 13-18-54-22           0476739                                                       
    103/13-18-054-22W5/00                                                                               


    -------------------- END OF WELL LICENCES DAILY LIST  -------------------- 

 

Thanks For your valuable response.

 

Rgards,

Sandeep

1 ACCEPTED SOLUTION
ImkeF
Super User
Super User

I agree with Greg that this is a challenge. But to my experience M is quite capable to resolve structures that our eyes can spot. In my approach I tried to transform the data in a way that the separator to split the fields are 2 blank spaces.

Admittedly this code is not robust so if it would be applied to different but similar text files it might not work instantly and you might need to adjust it. But for the example here it creates a nice table:

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("7VrbktsoEP0VKk9J1UoDuusRS9hmjcCLJHsnqfz/b2yDZOSLJMszjrMP6ZqLSoY+QJ/upsE/fnxBVqhYMd1QxCTTm3ek2aYVtFEaLZQvP/86qfr1shTsyIRAghdMFqxGvK5bViJUUi7e4XXdPBXsKfJ6MIdY0ob9RnjvF8oFonmwxJC0YldD6rmCZFuBO7jXFQevoAJpvtk2df9yo1UrS8QEO9CGKzk6PfPQSv5PyxAvmWz4mju9davXFMAK5Sldcgnrb3SvFNUlgnaiRDCaRptB7rX6mxUN0Ldk+2Y7vpZ2BnTLQKWgdQ1YhRtZp2+ZNExXZjhcbtB3Ja8XaQArNRfCtFJ7WJ9hEezy7lu9V/V5Z/saNe/785d1u6obahZ9XIaZGdvUbKrdIjktuVDFvM1exkaHuBLfUVkiTblcqSOKvDDyCM68/Hx8OMpwkIZoiNeFVkc3jSiP/Lianr4DIxi/YQeB82PyhjFCoIgEqZ+CCma05X5ktW2B9wh4zsSZMpIn2MfTaANL2AF9lcW3GbsgN+8Jqdp6xzZoPQU3gHENfgKmpWKiKYhkJyDwq7ItrqhQ6LZkSPEpDRc26xOmaEp/GnBGcHhuhhmwV4gDa1SrKwq+zdD2OzpQsaVCUESIBz9p5mV26JaNEZpgYxoTP1/Kxk4zBtU4G9iY4thPejaS1D6awEsl2GcPlNH8FA7CCD/AxjtkHCY8LsWWagHQgu7YGCcd2FZpDhG0mSPj09h4ZjPTulB6/zAlcQJUdGaYAXuFOLCC2vwogGdlDYtDwGFiD/6eB8eOjTGaYmMS+sEyNgZvHQCGf+CUkWWjSc154IegAswVpMQnVlvFSl5YP6HDfjKI0yfGRpg10/VttupFqCPsKSoq5QHS8S0hfw8bnc0+ESBJx8beDDNgrxAHtqeamqi4o1qj0Eu9JPbi69YdG40HjbIxJ7GfLc7UIXikhwEGx0NsRCG24RUiYEBOkXY0NiYkzp8XG+20p6UCgkn2Ppmqn8rGDa3Het2CGZtVsF9voLKtIU6aevBRRsKGieTODDNgr5DBz6QWho1MVIgYNqbYS69bd2w0r8djI06W7xtJx8bUpImzTB2mJzaGGenZNsrGGC/M1Kpt7rMRpn1UeqQi6uT/ycaCSlpyKqEEbVpbWA6k5BU3Zd5dwUDDxJlhBuwVMszswFBSeiRFKwql6zuCNJ16AYZkfTZ0y0azhVxrxrbqqjBN43jpvjF4wxYAW4Q+Uxs2xolNz8C5FCJtx+2Kl5Lulb6oIInJ5E/L1P2kp2Rxpn5pFQNltTq09SlRc1k8XsmYfeNghhmwV8hwZKDkBulWmvioWXmkDSw/bCoSL868IOiHbtlodpKjsTGJ0wdiY2Kd0mof2Bgm6YmNKDrFvrrxe8AzZSR5pIq5W1OfpjwuB74zZzf3a+qX7hudzdi/e6H6U6UHUzVkahMeezPMgL1CRtkIoWuckZaNGfDm02wMptiYBX/Y+IeNJlOvi0KgY5DsMUFrXm/BOIEXELOFjPuhGzYSNMXGJOvPZO6A2UxtVdsNSzzU1GkYWBVHoy3qa+qVkvK9S5GDkCx/Ihv76U5JVVSt1nRy4/gENn6tiy2r2LcTG1e8aSs2VuTfZGprtw+eOEJBHQTODDNgrxAHdmDchMU109pckJDY7BmjEMr/s9YdG03aHmUjARebo+NlprYI2EIcY3f6jbtCmZng1x2E28AFnLoiS4SD8Ils7Oc9IVI1fMeOXKJqNdbo91QxB3NNJEznj+8cITaemWEG7BXiwCj4YQm/TY3U+vIWeckN0eXFznDXCMUwan3u3/qtu3dUfo8OnGsUUI+Ws4jXii4+mh0W1YXdhYDX7Vm14pKaY9Uw8KK8O923gqPUeJ15bI/cde6OSKExhtY4Mw40v7SmQ/hIh+vrsYfFLfvHVZgl6vdqbonCmyWaGXS3xuGiNSbjg4buN0s9u3Rjg7b9p7tcDjrdMLdF3dHdkSLw0dxLQu88W8CgYyidzePV3fRl0rd9MXTGUX9YNC1JaPabd4kxPfSHxV5go6JtzF13/fpgcxkk3DdVCnMfDrufed//fKT56LCnRnTdeOaj5WAOsS7sQWfdytJ+XwB8kWReHLlK3goQM0nDfELjIkTzYP3OAmCLcNfvPgp2/1swkF7Lmzx08S2mcWr8/A8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Added Index2" = Table.AddIndexColumn(Source, "Index", 0, 1),
    #"Filtered Rows2" = Table.SelectRows(#"Added Index2", each [Index] > 9 and [Index]<77 and [Index]<>15),
    
// Some pogo dance to combine SURFACE CO-ORDINATES
    #"Replaced Value" = Table.ReplaceValue(#"Filtered Rows2"," N ","   N",Replacer.ReplaceText,{"Column1"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value"," S ","   S",Replacer.ReplaceText,{"Column1"}),
    #"Replaced Value2" = Table.ReplaceValue(#"Replaced Value1"," E ","E",Replacer.ReplaceText,{"Column1"}),
    #"Replaced Value3" = Table.ReplaceValue(#"Replaced Value2"," E ","E",Replacer.ReplaceText,{"Column1"}),
    #"Replaced Value4" = Table.ReplaceValue(#"Replaced Value3","E ","E",Replacer.ReplaceText,{"Column1"}),
    #"Replaced Value5" = Table.ReplaceValue(#"Replaced Value4"," W ","W",Replacer.ReplaceText,{"Column1"}),
    #"Replaced Value6" = Table.ReplaceValue(#"Replaced Value5"," W ","W",Replacer.ReplaceText,{"Column1"}),
    #"Replaced Value7" = Table.ReplaceValue(#"Replaced Value6"," N "," N",Replacer.ReplaceText,{"Column1"}),
    #"Replaced Value8" = Table.ReplaceValue(#"Replaced Value7"," N "," N",Replacer.ReplaceText,{"Column1"}),
    #"Replaced Value9" = Table.ReplaceValue(#"Replaced Value8"," S "," S",Replacer.ReplaceText,{"Column1"}),
    #"Replaced Value10" = Table.ReplaceValue(#"Replaced Value9"," S "," S",Replacer.ReplaceText,{"Column1"}),

// Number system for future transformations
    #"Added Index1" = Table.AddIndexColumn(#"Replaced Value10", "Index2", 1, 1),
    PositionInGroup = Table.AddColumn(#"Added Index1", "Position", each Number.Mod([Index2],6)),
    Group = Table.AddColumn(PositionInGroup, "Group", each Number.RoundUp([Index2]/6)),

// Some voodo dance to reduce spaces between expressions to 2 (no, this is not robust and might need to be adjusted)
    #"Replaced Value11" = Table.ReplaceValue(Group,"          ","  ",Replacer.ReplaceText,{"Column1"}),
    #"Replaced Value12" = Table.ReplaceValue(#"Replaced Value11","          ","  ",Replacer.ReplaceText,{"Column1"}),
    #"Replaced Value13" = Table.ReplaceValue(#"Replaced Value12","     ","  ",Replacer.ReplaceText,{"Column1"}),
    #"Replaced Value14" = Table.ReplaceValue(#"Replaced Value13","   ","  ",Replacer.ReplaceText,{"Column1"}),
    #"Replaced Value15" = Table.ReplaceValue(#"Replaced Value14","   ","  ",Replacer.ReplaceText,{"Column1"}),

// clean up
    #"Removed Columns" = Table.RemoveColumns(#"Replaced Value15",{"Index", "Index2"}),

// Pivot on groups
    #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Removed Columns", {{"Position", type text}}, "de-DE"), List.Distinct(Table.TransformColumnTypes(#"Removed Columns", {{"Position", type text}}, "de-DE")[Position]), "Position", "Column1"),

// clean up
    #"Removed Columns1" = Table.RemoveColumns(#"Pivoted Column",{"0"}),

// Some painfully repetitive split of pivoted columns
    #"Split Column by Delimiter1" = Table.SplitColumn(#"Removed Columns1","1",Splitter.SplitTextByDelimiter("  ", QuoteStyle.Csv),{"1.1", "1.2", "1.3", "1.4", "1.5", "1.6"}),
    #"Split Column by Delimiter2" = Table.SplitColumn(#"Split Column by Delimiter1","2",Splitter.SplitTextByDelimiter("  ", QuoteStyle.Csv),{"2.1", "2.2", "2.3", "2.4", "2.5", "2.6"}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Split Column by Delimiter2","3",Splitter.SplitTextByDelimiter("  ", QuoteStyle.Csv),{"3.1", "3.2", "3.3", "3.4", "3.5"}),
    #"Split Column by Delimiter3" = Table.SplitColumn(#"Split Column by Delimiter","4",Splitter.SplitTextByDelimiter("  ", QuoteStyle.Csv),{"4.1", "4.2", "4.3", "4.4", "4.5", "4.6", "4.7"}),
    #"Split Column by Delimiter4" = Table.SplitColumn(#"Split Column by Delimiter3","5",Splitter.SplitTextByDelimiter("  ", QuoteStyle.Csv),{"5.1", "5.2", "5.3", "5.4"}),

    #"Promoted Headers" = Table.PromoteHeaders(#"Split Column by Delimiter4"),
    #"Removed Other Columns" = Table.SelectColumns(#"Promoted Headers",{"WELL NAME", "LICENCENUMBER", " MINERAL RIGHTS", "GROUND ELEVATION", "UNIQUEIDENTIFIER", "SURFACECO-ORDINATES", "BOARD FIELD CENTRE", "LAHEECLASSIFICATION", "PROJECTED DEPTH", "FIELD", "TERMINATING ZONE", "DRILLING OPERATION", "WELL PURPOSE", "WELL", "TYPE", "LICENSEE", " SURFACELOCATION"}),
    #"Trimmed Text" = Table.TransformColumns(#"Removed Other Columns",{},Text.Trim),
    #"Cleaned Text" = Table.TransformColumns(#"Trimmed Text",{},Text.Clean)
in
    #"Cleaned Text"

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

7 REPLIES 7
ImkeF
Super User
Super User

I agree with Greg that this is a challenge. But to my experience M is quite capable to resolve structures that our eyes can spot. In my approach I tried to transform the data in a way that the separator to split the fields are 2 blank spaces.

Admittedly this code is not robust so if it would be applied to different but similar text files it might not work instantly and you might need to adjust it. But for the example here it creates a nice table:

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("7VrbktsoEP0VKk9J1UoDuusRS9hmjcCLJHsnqfz/b2yDZOSLJMszjrMP6ZqLSoY+QJ/upsE/fnxBVqhYMd1QxCTTm3ek2aYVtFEaLZQvP/86qfr1shTsyIRAghdMFqxGvK5bViJUUi7e4XXdPBXsKfJ6MIdY0ob9RnjvF8oFonmwxJC0YldD6rmCZFuBO7jXFQevoAJpvtk2df9yo1UrS8QEO9CGKzk6PfPQSv5PyxAvmWz4mju9davXFMAK5Sldcgnrb3SvFNUlgnaiRDCaRptB7rX6mxUN0Ldk+2Y7vpZ2BnTLQKWgdQ1YhRtZp2+ZNExXZjhcbtB3Ja8XaQArNRfCtFJ7WJ9hEezy7lu9V/V5Z/saNe/785d1u6obahZ9XIaZGdvUbKrdIjktuVDFvM1exkaHuBLfUVkiTblcqSOKvDDyCM68/Hx8OMpwkIZoiNeFVkc3jSiP/Lianr4DIxi/YQeB82PyhjFCoIgEqZ+CCma05X5ktW2B9wh4zsSZMpIn2MfTaANL2AF9lcW3GbsgN+8Jqdp6xzZoPQU3gHENfgKmpWKiKYhkJyDwq7ItrqhQ6LZkSPEpDRc26xOmaEp/GnBGcHhuhhmwV4gDa1SrKwq+zdD2OzpQsaVCUESIBz9p5mV26JaNEZpgYxoTP1/Kxk4zBtU4G9iY4thPejaS1D6awEsl2GcPlNH8FA7CCD/AxjtkHCY8LsWWagHQgu7YGCcd2FZpDhG0mSPj09h4ZjPTulB6/zAlcQJUdGaYAXuFOLCC2vwogGdlDYtDwGFiD/6eB8eOjTGaYmMS+sEyNgZvHQCGf+CUkWWjSc154IegAswVpMQnVlvFSl5YP6HDfjKI0yfGRpg10/VttupFqCPsKSoq5QHS8S0hfw8bnc0+ESBJx8beDDNgrxAHtqeamqi4o1qj0Eu9JPbi69YdG40HjbIxJ7GfLc7UIXikhwEGx0NsRCG24RUiYEBOkXY0NiYkzp8XG+20p6UCgkn2Ppmqn8rGDa3Het2CGZtVsF9voLKtIU6aevBRRsKGieTODDNgr5DBz6QWho1MVIgYNqbYS69bd2w0r8djI06W7xtJx8bUpImzTB2mJzaGGenZNsrGGC/M1Kpt7rMRpn1UeqQi6uT/ycaCSlpyKqEEbVpbWA6k5BU3Zd5dwUDDxJlhBuwVMszswFBSeiRFKwql6zuCNJ16AYZkfTZ0y0azhVxrxrbqqjBN43jpvjF4wxYAW4Q+Uxs2xolNz8C5FCJtx+2Kl5Lulb6oIInJ5E/L1P2kp2Rxpn5pFQNltTq09SlRc1k8XsmYfeNghhmwV8hwZKDkBulWmvioWXmkDSw/bCoSL868IOiHbtlodpKjsTGJ0wdiY2Kd0mof2Bgm6YmNKDrFvrrxe8AzZSR5pIq5W1OfpjwuB74zZzf3a+qX7hudzdi/e6H6U6UHUzVkahMeezPMgL1CRtkIoWuckZaNGfDm02wMptiYBX/Y+IeNJlOvi0KgY5DsMUFrXm/BOIEXELOFjPuhGzYSNMXGJOvPZO6A2UxtVdsNSzzU1GkYWBVHoy3qa+qVkvK9S5GDkCx/Ihv76U5JVVSt1nRy4/gENn6tiy2r2LcTG1e8aSs2VuTfZGprtw+eOEJBHQTODDNgrxAHdmDchMU109pckJDY7BmjEMr/s9YdG03aHmUjARebo+NlprYI2EIcY3f6jbtCmZng1x2E28AFnLoiS4SD8Ils7Oc9IVI1fMeOXKJqNdbo91QxB3NNJEznj+8cITaemWEG7BXiwCj4YQm/TY3U+vIWeckN0eXFznDXCMUwan3u3/qtu3dUfo8OnGsUUI+Ws4jXii4+mh0W1YXdhYDX7Vm14pKaY9Uw8KK8O923gqPUeJ15bI/cde6OSKExhtY4Mw40v7SmQ/hIh+vrsYfFLfvHVZgl6vdqbonCmyWaGXS3xuGiNSbjg4buN0s9u3Rjg7b9p7tcDjrdMLdF3dHdkSLw0dxLQu88W8CgYyidzePV3fRl0rd9MXTGUX9YNC1JaPabd4kxPfSHxV5go6JtzF13/fpgcxkk3DdVCnMfDrufed//fKT56LCnRnTdeOaj5WAOsS7sQWfdytJ+XwB8kWReHLlK3goQM0nDfELjIkTzYP3OAmCLcNfvPgp2/1swkF7Lmzx08S2mcWr8/A8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Added Index2" = Table.AddIndexColumn(Source, "Index", 0, 1),
    #"Filtered Rows2" = Table.SelectRows(#"Added Index2", each [Index] > 9 and [Index]<77 and [Index]<>15),
    
// Some pogo dance to combine SURFACE CO-ORDINATES
    #"Replaced Value" = Table.ReplaceValue(#"Filtered Rows2"," N ","   N",Replacer.ReplaceText,{"Column1"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value"," S ","   S",Replacer.ReplaceText,{"Column1"}),
    #"Replaced Value2" = Table.ReplaceValue(#"Replaced Value1"," E ","E",Replacer.ReplaceText,{"Column1"}),
    #"Replaced Value3" = Table.ReplaceValue(#"Replaced Value2"," E ","E",Replacer.ReplaceText,{"Column1"}),
    #"Replaced Value4" = Table.ReplaceValue(#"Replaced Value3","E ","E",Replacer.ReplaceText,{"Column1"}),
    #"Replaced Value5" = Table.ReplaceValue(#"Replaced Value4"," W ","W",Replacer.ReplaceText,{"Column1"}),
    #"Replaced Value6" = Table.ReplaceValue(#"Replaced Value5"," W ","W",Replacer.ReplaceText,{"Column1"}),
    #"Replaced Value7" = Table.ReplaceValue(#"Replaced Value6"," N "," N",Replacer.ReplaceText,{"Column1"}),
    #"Replaced Value8" = Table.ReplaceValue(#"Replaced Value7"," N "," N",Replacer.ReplaceText,{"Column1"}),
    #"Replaced Value9" = Table.ReplaceValue(#"Replaced Value8"," S "," S",Replacer.ReplaceText,{"Column1"}),
    #"Replaced Value10" = Table.ReplaceValue(#"Replaced Value9"," S "," S",Replacer.ReplaceText,{"Column1"}),

// Number system for future transformations
    #"Added Index1" = Table.AddIndexColumn(#"Replaced Value10", "Index2", 1, 1),
    PositionInGroup = Table.AddColumn(#"Added Index1", "Position", each Number.Mod([Index2],6)),
    Group = Table.AddColumn(PositionInGroup, "Group", each Number.RoundUp([Index2]/6)),

// Some voodo dance to reduce spaces between expressions to 2 (no, this is not robust and might need to be adjusted)
    #"Replaced Value11" = Table.ReplaceValue(Group,"          ","  ",Replacer.ReplaceText,{"Column1"}),
    #"Replaced Value12" = Table.ReplaceValue(#"Replaced Value11","          ","  ",Replacer.ReplaceText,{"Column1"}),
    #"Replaced Value13" = Table.ReplaceValue(#"Replaced Value12","     ","  ",Replacer.ReplaceText,{"Column1"}),
    #"Replaced Value14" = Table.ReplaceValue(#"Replaced Value13","   ","  ",Replacer.ReplaceText,{"Column1"}),
    #"Replaced Value15" = Table.ReplaceValue(#"Replaced Value14","   ","  ",Replacer.ReplaceText,{"Column1"}),

// clean up
    #"Removed Columns" = Table.RemoveColumns(#"Replaced Value15",{"Index", "Index2"}),

// Pivot on groups
    #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Removed Columns", {{"Position", type text}}, "de-DE"), List.Distinct(Table.TransformColumnTypes(#"Removed Columns", {{"Position", type text}}, "de-DE")[Position]), "Position", "Column1"),

// clean up
    #"Removed Columns1" = Table.RemoveColumns(#"Pivoted Column",{"0"}),

// Some painfully repetitive split of pivoted columns
    #"Split Column by Delimiter1" = Table.SplitColumn(#"Removed Columns1","1",Splitter.SplitTextByDelimiter("  ", QuoteStyle.Csv),{"1.1", "1.2", "1.3", "1.4", "1.5", "1.6"}),
    #"Split Column by Delimiter2" = Table.SplitColumn(#"Split Column by Delimiter1","2",Splitter.SplitTextByDelimiter("  ", QuoteStyle.Csv),{"2.1", "2.2", "2.3", "2.4", "2.5", "2.6"}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Split Column by Delimiter2","3",Splitter.SplitTextByDelimiter("  ", QuoteStyle.Csv),{"3.1", "3.2", "3.3", "3.4", "3.5"}),
    #"Split Column by Delimiter3" = Table.SplitColumn(#"Split Column by Delimiter","4",Splitter.SplitTextByDelimiter("  ", QuoteStyle.Csv),{"4.1", "4.2", "4.3", "4.4", "4.5", "4.6", "4.7"}),
    #"Split Column by Delimiter4" = Table.SplitColumn(#"Split Column by Delimiter3","5",Splitter.SplitTextByDelimiter("  ", QuoteStyle.Csv),{"5.1", "5.2", "5.3", "5.4"}),

    #"Promoted Headers" = Table.PromoteHeaders(#"Split Column by Delimiter4"),
    #"Removed Other Columns" = Table.SelectColumns(#"Promoted Headers",{"WELL NAME", "LICENCENUMBER", " MINERAL RIGHTS", "GROUND ELEVATION", "UNIQUEIDENTIFIER", "SURFACECO-ORDINATES", "BOARD FIELD CENTRE", "LAHEECLASSIFICATION", "PROJECTED DEPTH", "FIELD", "TERMINATING ZONE", "DRILLING OPERATION", "WELL PURPOSE", "WELL", "TYPE", "LICENSEE", " SURFACELOCATION"}),
    #"Trimmed Text" = Table.TransformColumns(#"Removed Other Columns",{},Text.Trim),
    #"Cleaned Text" = Table.TransformColumns(#"Trimmed Text",{},Text.Clean)
in
    #"Cleaned Text"

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

Nice one Imke!


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Thank you so much all of you, specially thanks to @ImkeF , this solution is working for me.

I think it is worthy to point out that the starting data set of the original post is a report output - and that the data itself within that system/database is very likely stored in table format.

 

Perhaps that is obvious to everyone, am not sure.  But it seems worthwhile in some cases when establishing the initial data model for a Power BI application to at least attempt to go back to the data source and request the data in basic table output - rather than the report.  Just saying - cause this solution is very very impressive - but some reports just won't be able to be handled & reformated reliably.

 

 

www.CahabaData.com

Absolutely true and important: Before reporting your data, try get to know it better: Where does it come from, what does it have to offer and is it reliable are the minimum-questions. 

 

Get into a dialog with the system owners from the business side and the IT-side. This will be necessary sooner or later anyway - so better start right at the beginning. Power BI might be the tool where everyone can see own benefits instantly (... maybe even moving this ugly report to it completely 🙂 )

 

So thanks both of you for pointing this out (& for the kudos 🙂 ).

 

 

 

 

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

Greg_Deckler
Super User
Super User

This is as far as I got, not sure it is useful...

 

let
    Source = Csv.Document(File.Contents("C:\temp\powerbi\UglyTextFile\uglytextfile.txt"),null,{0, 41, 49, 70},null,1252),
    #"Removed Top Rows" = Table.Skip(Source,17),
    #"Removed Bottom Rows" = Table.RemoveLastN(#"Removed Top Rows",33),
    #"Transposed Table" = Table.Transpose(#"Removed Bottom Rows"),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Transposed Table", {}, "Attribute", "Value")
in
    #"Unpivoted Columns"

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Greg_Deckler
Super User
Super User

Wow, that is uh...uh...uh...ugly!! 🙂

 

What parts of this text file are you trying to import, the "table" area or all of it including the stuff after the 

AMENDMENTS OF WELL LICENCES

area? Honestly, I'm not aware of any tool that could *easily* consume such a text file. I think even Perl would have challenges parsing something like that and it was originally built to do text parsing. Is this text file being generated from something that you have control over? You might have better success changing the output format to something that is a bit more standard. I will see if I can play around with it and get something out of it.

 

 


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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.