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
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