Forum Discussion

JeffJ625's avatar
JeffJ625
Copper Contributor
Sep 15, 2019

Formatting Issue (Getting data from 1 column into 7 columns)

Hello. I'm having a devil of a problem trying to import data from the web and getting it into Excel in roughly the same columned format.  I'm copying the relevant data (which is information that I've...
excel
Subodh_Tiwari_sktneer's avatar
Subodh_Tiwari_sktneer
Silver Contributor
Sep 15, 2019

JeffJ625 

You may use Power Query to get the data in the desired format.

To do so, follow these steps...

 

  1. Convert your one column Web Data into an Excel Table if not already formatted as an Excel Table and to do that select your data and press Ctrl+T and uncheck the CheckBox for "My table has headers" and click OK.
  2. Rename the Excel Table you just created with "WebData".
  3. Now go to Data Tab and  under Get & Transform Data group, click on Get Data --> From Other Sources --> Blank Query. This will open Power Query Editor Window.
  4. On Power Query Editor Window --> View --> and click on Advanced Editor and delete whatever you see that and paste the query given below in the Advanced Editor and click on OK.
  5. Now go to Home Tab on Power Query Editor Window and click on Close & Load. This will load the table with data in the desired format into a new sheet.

 

Query:

 

let
    Source = Excel.CurrentWorkbook(){[Name="WebData"]}[Content],
    #"Removed Blank Rows" = Table.SelectRows(Source, each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {"", null}))),
    #"Changed Type" = Table.TransformColumnTypes(#"Removed Blank Rows",{{"Column1", type any}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1),
    #"Inserted Modulo" = Table.AddColumn(#"Added Index", "Modulo", each Number.Mod([Index], 7), type number),
    #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Inserted Modulo", {{"Modulo", type text}}, "en-IN"), List.Distinct(Table.TransformColumnTypes(#"Inserted Modulo", {{"Modulo", type text}}, "en-IN")[Modulo]), "Modulo", "Column1"),
    #"Removed Columns" = Table.RemoveColumns(#"Pivoted Column",{"Index"}),
    #"Filled Up" = Table.FillUp(#"Removed Columns",{"1", "2", "3", "4", "5", "6"}),
    #"Filtered Rows" = Table.SelectRows(#"Filled Up", each ([0] <> null)),
    #"Renamed Columns" = Table.RenameColumns(#"Filtered Rows",{{"0", "Company Name"}, {"1", "Industry"}, {"2", "Yearly Revenue"}, {"3", "Phone#"}, {"4", "Location"}, {"5", "Website"}, {"6", "Year Founded"}})
in
    #"Renamed Columns"

 

 

Also, if you are getting the web data through the From Web under Get & Transformed Data Group, you may tweak the existing query to get the data in the desired format.

SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Sep 15, 2019

Subodh_Tiwari_sktneer 

Bit another transformation

let

    ColumnNames = {"Company Name", "Industry", "Yearly Revenue", "Phone#", "Location", "Website", "Year Founded"},
    ItemsInGroup =7,

    Source = Excel.CurrentWorkbook(){[Name="WebData"]}[Content],
    RemoveNulls = Table.SelectRows(
        Source,
        each not List.IsEmpty(
            List.RemoveMatchingItems(Record.FieldValues(_), {"", null})
        )
    ),
    GroupsToLists = Table.FromList(
        List.Split(
            RemoveNulls[Column1],
            ItemsInGroup
        ),
        Splitter.SplitByNothing()
    ),
    GroupsToRecords = Table.ReplaceValue(
        GroupsToLists,
        each [Column1] ,
        each Record.FromList([Column1],ColumnNames),
        Replacer.ReplaceValue,{"Column1"}
    ),
    ExpandRecords = Table.ExpandRecordColumn(
        GroupsToRecords,
        "Column1", ColumnNames, ColumnNames
    )
in
    ExpandRecords

Resources