Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to Solution.
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
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!
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.
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
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"
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.
User | Count |
---|---|
141 | |
113 | |
104 | |
78 | |
64 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |