Forum Discussion
Solved
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
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"
7 Replies
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"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.
Nice one Imke!
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.
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"
