Forum Discussion
Solved
Importing Data form Text
How can I import data from Text file. My text file formate as below. ALBERTA ENERGY REGULATOR
...
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"
ImkeF
Community Champion
Community ChampionI 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.
- CahabaData
Memorable Member
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.
- ImkeF
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 :-) ).
