Import a Text file not working

[amirabedhiafi]

I have the following data in a text file and I want to import it using M language : 

"country": "afghanistan",
"capital": "kabul",
"currency": "afghani",
"native_language": ["dari persian", "pashto"],
"famous_for": "rugs, taliban",
"phone_code": "+93",
"flag": "https://flagpedia.net/data/flags/h80/af.png",
"drive_direction": "right",
"alcohol_prohibition": "nationwide",
"area":
"km2": 652864,
"mi2": 252072

"continent": "as",
"iso":
"numeric": "004",
"alpha_2": "af",
"alpha_3": "afg"

"tld": ".af",
"constitutional_form": "republic",
"language_codes": ["fa-AF", "ps-AF"],
"is_landlocked": true,
"neighbors": ["cn", "ir", "pk", "tj", "tm", "uz"]

I want to import the previous data into a table.

This is the first step I did : 

Source= Csv.Document(File.Contents(FileLocation),[Delimiter=":", Columns=3, Encoding=1252, QuoteStyle=QuoteStyle.Csv])

But this is what I get :

I want to have instead of Column1 Country (Column2 Capital...)

[Jimmy801 · Accepted solution]

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

[Jimmy801]

Hello @amirabedhiafi 

did this help a thing?

BR

Jimmy

[amirabedhiafi]

@Jimmy801  When I change the source to : 

Source=Lines.FromBinary(File.Contents(MyPath),QuoteStyle.CSV, null, 1252) 

I get empty data starting from the groupedrows step

[Jimmy801]

Hello @amirabedhiafi 

I've put your provided code two times into one text file and applied this code

let
    Source =  Lines.FromBinary(File.Contents("YOURTEXTFILEWITH2TIMESYOURTEXTINIT"), QuoteStyle.Csv, true, 1252),
    #"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"

and this is the outcome

if this is not working, your textfile doesn't contain the text you provided. In case post your text-file here, then I can have another look

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

[edhans]

If that is all the data you have, you can just transpose the table. If you have more data though, then @Anonymous is correct, you will need to pivot column 1, and tell it not to aggregate and use column2 as your values. However, you may need to add an index column first for this to work, then remove the index afterwards. It is needed when you have limited columns and doing a Pivot operation. 

A good sample of data and expected output would help. How to get good help fast. Help us help you.
How to Get Your Question Answered Quickly - Give us a good and concise explanation
How to provide sample data in the Power BI Forum - Provide data in a table format per the link, or share an Excel/CSV file via OneDrive, Dropbox, etc.. Provide expected output using a screenshot of Excel or other image. Do not provide a screenshot of the source data. I cannot paste an image into Power BI tables.

[Anonymous]

Hi @amirabedhiafi

It would be great if you could post the desired outcome of what you want to do, the tech description is a bit unclear. 

But by the sound of it, you want to pivot Column1. You can find this command on the Transform tab as far as I remember.

Kind regards

JB

[amirabedhiafi]

This is my desired output :