Forum Discussion

brandanb's avatar
brandanb
Regular Visitor
6 years ago
Solved

Problems when importing CSV file

I have a CSV file that I've imported and set "comma" as the delimiter. However, the way the CSV was formatted, I'm running into issues. Below, table 1, is what Power BI & Excel automatically transfor...
Need Help
v-deddai1-msft's avatar
v-deddai1-msft
Icon for Community Support rankCommunity Support
6 years ago

Hi brandanb ,

 

Would you please refer to the M query:

 

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WysrPyEvJT1XSUYqIjDI0MjbRUXB0coYwXFzdQAylWJ1opcSczORUiMJwVyeIvFuQK4ThFBYJURgLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Username = _t, ValidSerialNumbers = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Username", type text}, {"ValidSerialNumbers", type text}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "ValidSerialNumbers", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"ValidSerialNumbers.1", "ValidSerialNumbers.2", "ValidSerialNumbers.3"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"ValidSerialNumbers.1", type text}, {"ValidSerialNumbers.2", type text}, {"ValidSerialNumbers.3", type text}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type1", {"Username"}, "Attribute", "Value"),
    #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Other Columns",{"Attribute"})
in
    #"Removed Columns"

 

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

Best Regards,

Dedmon Dai