Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I have a CSV file that has multiple tables in that are seperated vertically. I want to know how to import these into tables (preferrably in 1 or 2 steps) based on a blank (or multiple blank) row/s.
So the following:
csv.csv
2021-05-01 | 2021-05-02 | 2021-05-03 | 2021-05-04 |
Apples | Banana | Orange | Apple |
Banana | Apple | Mango | Watermelon |
01-04-2021 | 02-04-2021 | 03-04-2021 | 04-04-2021 |
Sunflower | Poppy | Rose | Daffodil |
Lilly | Sunflower | Cornflower | Poppy |
Would end up like:
Table1
2021-05-01 | 2021-05-02 | 2021-05-03 | 2021-05-04 |
Apples | Banana | Orange | Apple |
Banana | Apple | Mango | Watermelon |
Table2
01-04-2021 | 02-04-2021 | 03-04-2021 | 04-04-2021 |
Sunflower | Poppy | Rose | Daffodil |
Lilly | Sunflower | Cornflower | Poppy |
Solved! Go to Solution.
I missed that multiple tables...you need to group the rows into different tables, another way of increasing index, you can take from here
let
Source = Csv.Document(File.Contents("C:\Users\----\Book1.csv"),[Delimiter=",", Encoding=1252, QuoteStyle=QuoteStyle.None]),
checkBlank = Table.CombineColumns(Source, Table.ColumnNames(Source), (x)=> if List.MatchesAll(x,each _ = "") then 1 else 0,"check"),
#"Added Index" = Table.AddIndexColumn(checkBlank, "Index", 0, 1, Int64.Type),
#"Added Custom" = Table.AddColumn(#"Added Index", "Group", each if [Index]=0 then 0
else if [check] = 0 then List.Sum(List.FirstN( checkBlank[check], [Index]))
else -1),
SouceIndex = Table.AddIndexColumn(Source, "Index", 0,1),
#"Merged Queries" = Table.NestedJoin(SouceIndex, {"Index"}, #"Added Custom", {"Index"}, "Group", JoinKind.LeftOuter),
#"Expanded Group" = Table.ExpandTableColumn(#"Merged Queries", "Group", {"check", "Group"}, {"check", "Group"}),
#"Filtered Rows" = Table.SelectRows(#"Expanded Group", each ([check] = 0)),
#"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Index", "check"}),
#"Grouped Rows" = Table.Group(#"Removed Columns", {"Group"}, {{"allrows", each _, type table }})
in
#"Grouped Rows"
Your two resulting tables are very similar (date headers with 1+ rows of category values). It would probably be better to unpivot and append them to result in one table. That will likely make your visualization/analysis easier. Would that work? If so, the community could suggest a specific query solution given your data.
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
I see what you mean, though unfortunately the info is unrelated and I need them all in seperate tables.
So you have no idea how many blank rows, I have tried one way
first table
let
Source = Csv.Document(File.Contents("C:\Users\----\Book1.csv"),[Delimiter=",", Encoding=1252, QuoteStyle=QuoteStyle.None]),
checkBlank = Table.CombineColumns( Source, Table.ColumnNames(Source), (x)=> if List.MatchesAll(x,each _ = "") then 1 else 0,"check"),
firstTable = Table.FirstN(Source, List.PositionOf( checkBlank[check],1,0))
in
firstTable
second table
let
Source = Csv.Document(File.Contents("C:\Users\----\Book1.csv"),[Delimiter=",", Encoding=1252, QuoteStyle=QuoteStyle.None]),
checkBlank = Table.CombineColumns( Source, Table.ColumnNames(Source), (x)=> if List.MatchesAll(x,each _ = "") then 1 else 0,"check"),
secondTable = Table.Skip(Source, List.PositionOf( checkBlank[check],1, List.Count( List.Select( checkBlank[check], each _=1))-1)+1)
in
secondTable
Yes that looks like it would work, however I don't know how many blank rows and hence how many tables there will be. I have tried to index each none blank section and increment them here: How-to-increment-a-number-variable-if-column-value-equals thinking that I could somehow use this info to create indexed tables, but so far no luck. This is a common csv output format from many of the tools we use at work so I thought there would be some sort of standardised way to do this.
I missed that multiple tables...you need to group the rows into different tables, another way of increasing index, you can take from here
let
Source = Csv.Document(File.Contents("C:\Users\----\Book1.csv"),[Delimiter=",", Encoding=1252, QuoteStyle=QuoteStyle.None]),
checkBlank = Table.CombineColumns(Source, Table.ColumnNames(Source), (x)=> if List.MatchesAll(x,each _ = "") then 1 else 0,"check"),
#"Added Index" = Table.AddIndexColumn(checkBlank, "Index", 0, 1, Int64.Type),
#"Added Custom" = Table.AddColumn(#"Added Index", "Group", each if [Index]=0 then 0
else if [check] = 0 then List.Sum(List.FirstN( checkBlank[check], [Index]))
else -1),
SouceIndex = Table.AddIndexColumn(Source, "Index", 0,1),
#"Merged Queries" = Table.NestedJoin(SouceIndex, {"Index"}, #"Added Custom", {"Index"}, "Group", JoinKind.LeftOuter),
#"Expanded Group" = Table.ExpandTableColumn(#"Merged Queries", "Group", {"check", "Group"}, {"check", "Group"}),
#"Filtered Rows" = Table.SelectRows(#"Expanded Group", each ([check] = 0)),
#"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Index", "check"}),
#"Grouped Rows" = Table.Group(#"Removed Columns", {"Group"}, {{"allrows", each _, type table }})
in
#"Grouped Rows"
Perfect, thank you so much!
I just had one last question on this, do you know how I promote the headers of all the tables when they are grouped (from the very last step) without having to open each?
EDIT: All good, got this one worked out:
#"Promote Headers" = Table.TransformColumns(#"Grouped Rows", {{"allrows", each Table.PromoteHeaders(_, [PromoteAllScalars = true])}})
in
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.