Import a Text file not working

Hello amirabedhiafi 

using here Csv.Document is already the wrong approach. Use Lines.FromBinary instead. After that you need some transformation to get to the result you need. Especially because you have sometimes a second level.

Here an example how to transform your data into a real table. Be aware that I used here some Lines.FromText that you are able to simple copy paste my code into the advanced editor. You have to change the source step into Lines.FromBinary(File.Contents(...),QuoteStyle.CSV, null, 1252),

let
    Source =  Lines.FromText("""country"": ""afghanistan"",#(cr)#(lf)""capital"": ""kabul"",#(cr)#(lf)""currency"": ""afghani"",#(cr)#(lf)""native_language"": [""dari persian"", ""pashto""],#(cr)#(lf)""famous_for"": ""rugs, taliban"",#(cr)#(lf)""phone_code"": ""+93"",#(cr)#(lf)""flag"": ""https://flagpedia.net/data/flags/h80/af.png"",#(cr)#(lf)""drive_direction"": ""right"",#(cr)#(lf)""alcohol_prohibition"": ""nationwide"",#(cr)#(lf)""area"":#(cr)#(lf)""km2"": 652864,#(cr)#(lf)""mi2"": 252072#(cr)#(lf)#(cr)#(lf)""continent"": ""as"",#(cr)#(lf)""iso"":#(cr)#(lf)""numeric"": ""004"",#(cr)#(lf)""alpha_2"": ""af"",#(cr)#(lf)""alpha_3"": ""afg""#(cr)#(lf)#(cr)#(lf)""tld"": "".af"",#(cr)#(lf)""constitutional_form"": ""republic"",#(cr)#(lf)""language_codes"": [""fa-AF"", ""ps-AF""],#(cr)#(lf)""is_landlocked"": true,#(cr)#(lf)""neighbors"": [""cn"", ""ir"", ""pk"", ""tj"", ""tm"", ""uz""],#(cr)#(lf)#(cr)#(lf)""country"": ""afghanistan"",#(cr)#(lf)""capital"": ""kabul"",#(cr)#(lf)""currency"": ""afghani"",#(cr)#(lf)""native_language"": [""dari persian"", ""pashto""],#(cr)#(lf)""famous_for"": ""rugs, taliban"",#(cr)#(lf)""phone_code"": ""+93"",#(cr)#(lf)""flag"": ""https://flagpedia.net/data/flags/h80/af.png"",#(cr)#(lf)""drive_direction"": ""right"",#(cr)#(lf)""alcohol_prohibition"": ""nationwide"",#(cr)#(lf)""area"":#(cr)#(lf)""km2"": 652864,#(cr)#(lf)""mi2"": 252072#(cr)#(lf)#(cr)#(lf)""continent"": ""as"",#(cr)#(lf)""iso"":#(cr)#(lf)""numeric"": ""004"",#(cr)#(lf)""alpha_2"": ""af"",#(cr)#(lf)""alpha_3"": ""afg""#(cr)#(lf)#(cr)#(lf)""tld"": "".af"",#(cr)#(lf)""constitutional_form"": ""republic"",#(cr)#(lf)""language_codes"": [""fa-AF"", ""ps-AF""],#(cr)#(lf)""is_landlocked"": true,#(cr)#(lf)""neighbors"": [""cn"", ""ir"", ""pk"", ""tj"", ""tm"", ""uz""]"),
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Converted to Table", "Column1", Splitter.SplitTextByEachDelimiter({":"}, QuoteStyle.Csv, false), {"Column1.1", "Column1.2"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Column1.1", type text}, {"Column1.2", type text}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type","#(cr,lf)","",Replacer.ReplaceText,{"Column1.1", "Column1.2"}),
    #"Added Custom" = Table.AddColumn(#"Replaced Value", "Custom", each if [Column1.2]="" then [Column1.1] else  if [Column1.1]="" then "" else  null),
    #"Filled Down" = Table.FillDown(#"Added Custom",{"Custom"}),
    #"Filtered Rows" = Table.SelectRows(#"Filled Down", each ([Column1.1] <> "") and ([Column1.2] <> "")),
    #"Added Custom1" = Table.AddColumn(#"Filtered Rows", "Custom.1", each if [Custom]=null or [Custom]="" then [Column1.1] else [Custom]&": "&[Column1.1]),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom1",{"Column1.2", "Custom.1"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Removed Other Columns",{"Custom.1", "Column1.2"}),
    #"Grouped Rows" = Table.Group(#"Reordered Columns", {"Custom.1"}, {{"AllRows", each Table.PromoteHeaders(Table.Transpose(_)) }}, GroupKind.Local, (g,c)=> if g[#"Custom.1"]= c[#"Custom.1"] then -1 else 0),
    #"Removed Other Columns1" = Table.SelectColumns(#"Grouped Rows",{"AllRows"}),
    #"Expanded AllRows" = Table.ExpandTableColumn(#"Removed Other Columns1", "AllRows", {"country", "capital", "currency", "native_language", "famous_for", "phone_code", "flag", "drive_direction", "alcohol_prohibition", "area: km2", "area: mi2", "continent", "iso: numeric", "iso: alpha_2", "iso: alpha_3", "tld", "constitutional_form", "language_codes", "is_landlocked", "neighbors"}, {"country", "capital", "currency", "native_language", "famous_for", "phone_code", "flag", "drive_direction", "alcohol_prohibition", "area: km2", "area: mi2", "continent", "iso: numeric", "iso: alpha_2", "iso: alpha_3", "tld", "constitutional_form", "language_codes", "is_landlocked", "neighbors"})

    
in
    #"Expanded AllRows"

Outcome:

Copy paste this code to the advanced editor in a new blank query to see how the solution works.

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy