Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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 transform the CSV into. Table 2 is what I need the data to look like. How do I transform this?
1.)
Username | ValidSerialNumbers |
johndoe | XYZ1234, ABC1234, DEF1234 |
alicedoe | WEB1234, FRE1234, BVY1234 |
2.)
Username | ValidSerialNumbers |
johndoe | XYZ1234 |
johndoe | ABC1234 |
johndoe | DEF1234 |
alicedoe | WEB1234 |
alicedoe | FRE1234 |
alicedoe | BVY1234 |
Solved! Go to Solution.
@brandanb , In data transformation/edit query you have an option to split column. Split column into rows. use that
check steps
https://www.tutorialgateway.org/how-to-split-columns-in-power-bi/
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
@brandanb , In data transformation/edit query you have an option to split column. Split column into rows. use that
check steps
https://www.tutorialgateway.org/how-to-split-columns-in-power-bi/
User | Count |
---|---|
77 | |
74 | |
62 | |
61 | |
45 |
User | Count |
---|---|
108 | |
102 | |
93 | |
83 | |
64 |