Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago
Solved

ExpandTableColumn for csv format using custom column

Hi,

 

How can I expand all the csv format custom table in one go?

 

 

 

 

Normally below code works for excel contents but it doesn't seem to work with csv files as all values shows null. 

 

Normally use with excel contents

#"Added Custom" = Table.AddColumn(#"Kept First Rows1", "Custom", each Excel.Workbook([Content])),
#"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Name", "Data", "Item", "Kind", "Hidden"}, {"Custom.Name", "Custom.Data", "Custom.Item", "Custom.Kind", "Custom.Hidden"}),
 
 
 
What I'm trying now:
 
let
// Get list of files in SharePoint folder
Source = SharePoint.Contents("https://XXX.sharepoint.com"),
// Get list of files in SharePoint folder
Documents = Source{[Name="Documents"]}[Content],
// Get list of files in SharePoint folder
#"KR Cell Status" = Documents{[Name="KR Cell Status"]}[Content],
// Get list of files in SharePoint folder
#"LGUplus" = #"KR Cell Status"{[Name="LGUplus"]}[Content],
// Get list of files in SharePoint folder
#"5G_DG1" = #"LGUplus"{[Name="5G_DG1"]}[Content],
#"Removed Other Columns" = Table.SelectColumns(#"5G_DG1",{"Content", "Name"}),
#"Sorted Rows" = Table.Sort(#"Removed Other Columns",{{"Name", Order.Descending}}),
#"Kept First Rows1" = Table.FirstN(#"Sorted Rows",5),
#"Added Custom" = Table.AddColumn(#"Kept First Rows1", "Custom", each Csv.Document([Content])),
#"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Name", "Data", "Item", "Kind", "Hidden"}, {"Custom.Name", "Custom.Data", "Custom.Item", "Custom.Kind", "Custom.Hidden"}),

 
 
So go back to Addded Custom step, 

 

If I click Table to expand:
 

 


 

let
// Get list of files in SharePoint folder
Source = SharePoint.Contents("https://XXXX.sharepoint.com/sites/XXX"),
// Get list of files in SharePoint folder
Documents = Source{[Name="Documents"]}[Content],
// Get list of files in SharePoint folder
#"KR Cell Status" = Documents{[Name="KR Cell Status"]}[Content],
// Get list of files in SharePoint folder
#"LGUplus" = #"KR Cell Status"{[Name="LGUplus"]}[Content],
// Get list of files in SharePoint folder
#"5G_DG1" = #"LGUplus"{[Name="5G_DG1"]}[Content],
#"Removed Other Columns" = Table.SelectColumns(#"5G_DG1",{"Content", "Name"}),
#"Sorted Rows" = Table.Sort(#"Removed Other Columns",{{"Name", Order.Descending}}),
#"Kept First Rows1" = Table.FirstN(#"Sorted Rows",5),
#"Added Custom" = Table.AddColumn(#"Kept First Rows1", "Custom", each Csv.Document([Content])),
#"2025_W11_5G_DG1 csv" = #"Added Custom"{[Name="2025_W11_5G_DG1.csv"]}[Custom]
in
#"2025_W11_5G_DG1 csv"

 

 

 

If I clisk Binary:

 

 

let
// Get list of files in SharePoint folder
Source = SharePoint.Contents("https://XXX.sharepoint.com/sites/XXX"),
// Get list of files in SharePoint folder
Documents = Source{[Name="Documents"]}[Content],
// Get list of files in SharePoint folder
#"KR Cell Status" = Documents{[Name="KR Cell Status"]}[Content],
// Get list of files in SharePoint folder
#"LGUplus" = #"KR Cell Status"{[Name="LGUplus"]}[Content],
// Get list of files in SharePoint folder
#"5G_DG1" = #"LGUplus"{[Name="5G_DG1"]}[Content],
#"Removed Other Columns" = Table.SelectColumns(#"5G_DG1",{"Content", "Name"}),
#"Sorted Rows" = Table.Sort(#"Removed Other Columns",{{"Name", Order.Descending}}),
#"Kept First Rows1" = Table.FirstN(#"Sorted Rows",5),
#"Added Custom" = Table.AddColumn(#"Kept First Rows1", "Custom", each Csv.Document([Content])),
#"2025_W11_5G_DG1 csv" = #"Added Custom"{[Name="2025_W11_5G_DG1.csv"]}[Content],
#"Imported CSV" = Csv.Document(#"2025_W11_5G_DG1 csv",[Delimiter=",", Columns=54, Encoding=1252, QuoteStyle=QuoteStyle.None])
in
#"Imported CSV"

 

 

 

How csv looks:

 

General Comment
Need Help
Show and Tell
Tips and Tricks
  • danextian's avatar
    danextian
    1 year ago

    Hi Anonymous 

     

    Use Csv.Document function. Sample code

    Table.PromoteHeaders( Csv.Document([Content], [Delimiter=",", Encoding=1252, QuoteStyle=QuoteStyle.Csv]), [PromoteAllScalars = true])

    [Content] refers to the binary column

    Since your colum header is not in row 1, you will need to skip a row before applying Table.PromoteHeaders 

    Table.PromoteHeaders( Table.Skip( Csv.Document([Content], [Delimiter=",", Encoding=1252, QuoteStyle=QuoteStyle.Csv]), 1), [PromoteAllScalars = true])

1 Reply

  • danextian's avatar
    danextian
    Super User
    1 year ago

    Hi Anonymous 

     

    Use Csv.Document function. Sample code

    Table.PromoteHeaders( Csv.Document([Content], [Delimiter=",", Encoding=1252, QuoteStyle=QuoteStyle.Csv]), [PromoteAllScalars = true])

    [Content] refers to the binary column

    Since your colum header is not in row 1, you will need to skip a row before applying Table.PromoteHeaders 

    Table.PromoteHeaders( Table.Skip( Csv.Document([Content], [Delimiter=",", Encoding=1252, QuoteStyle=QuoteStyle.Csv]), 1), [PromoteAllScalars = true])