Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Splitting 1 large text body into several columns

So I have an issue where I this large chunk of text which I can not transform before I load it into Power BI:

 

"ubff-prd99.74 %98.90 %100.00 %99.78 %cecartsservice-prd81.59 %21.01 %0.00 %88.40%cecustomersservice-prd81.60 %21.05 %0.00 %88.41 %ceservice-prd98.99 %97.99%100.00 %99.37 %cemerchantsservice-prd81.66 %21.30 %0.00 %88.45%ceordersservice-prd81.66 %21.29 %0.00 %88.44 %cepaymentsservice-prd62.40 %21.30%0.00 %76.30 %cepricingservice-prd81.66 %21.30 %0.00 %88.44%ceproductsservice-prd81.66 %21.31 %0.00 %88.44%smt-commerceengine-acl-live82.54 %25.09 %0.00 %89.00%smt-configserver-svc-live82.07 %23.08 %0.00 %88.70 %smt-dashboard-fed-live0.00%0.00 %0.00 %0.00 %smt-dealerships-fed-live0.00 %0.00 %0.00 %0.00%smt-gateway-svc-live82.06 %23.06 %0.00 %88.70 %smt-orders-api-live82.06 %23.04%0.00 %88.69 %smt-orders-fed-live0.00 %0.00 %0.00 %0.00 %smt-pim-acl-live82.06%23.08 %0.00 %88.69 %smt-provisioning-svc-live0.00 %0.00 %0.00 %0.00%smt-settings-api-live82.07 %23.08 %0.00 %88.69 %smt-settings-fed-live0.00 %0.00%0.00 %0.00 %"

 

I want to create a row for each item with the 4 percentages of each item in the next columns in this kind of format:

NamePercentage 1Percentage 2Percentage 3Percentag 4
ubff-prd99.74 %98.90 %100.00 %99.78 %
cecartsservice-prd........
...    

 

How can I do this in the Power Query Editor? 

 

Thanks for any help!

Need Help
  • AntrikshSharma's avatar
    AntrikshSharma
    3 years ago

    Anonymous Use this:

    let
    Source = Table.FromRows (
        Json.Document (
            Binary.Decompress (
                Binary.FromText (
                    "jZLdjoIwEEZfhZhw2aYgFPosxotaBm0iP2mRjW+/04Laimv2xknMmW/OTDkcdrdT25LRNELQqkhSUVPBkjRjjDKs7t86SRUoaSZrwcxagcPrjJYiSfOMsixJF7iuacEce7PT0IF54zlb+DLkMxcecG4+5ooKS2ixrxyIoeoi+3cTzn3ynoXJJfKDabYaC5yLEC5c+ijvHcThPMeV1vAVr7gfhLjRSvfnf6gUnh6am/rLPItx201EDZ1bF6A/6x6IVFdy1TPUOS3RNi8pe20gsKxNfau9EhhiZ/VoYXi+fE9ZHcypsLieRtrLaZCmIS00vsEhKxf+ehjkFU960aON8GTDe/wsJ/iR90iFLyr8g8ryXkSO+p0uXjAXEfzdYkFH3YUHZHxzjEcoPtOsrR56fNmn9ZcFLUyT+wgi5w/HfuQ/+a12NGB3PP4C",
                    BinaryEncoding.Base64
                ),
                Compression.Deflate
            )
        ),
        let
            _t = ( ( type nullable text ) meta [ Serialized.Text = true ] )
        in
            type table [ Column1 = _t ]
    ),
    ChangedType = Table.TransformColumnTypes ( Source, { { "Column1", type text } } ),
    AddedCustom = 
    Table.AddColumn (
        ChangedType,
        "Split",
        ( Data ) =>
            let
                SplitToRows = 
                    Splitter.SplitTextByCharacterTransition (
                        { "%" },
                        { "a" .. "z", "A" .. "Z" }
                    )( Data[Column1] ),
                SplitNamesAndPercentage = 
                    List.Transform (
                        SplitToRows,
                        each Splitter.SplitTextByCharacterTransition (
                            { "a" .. "z", "A" .. "Z" },
                            { "0" .. "9" }
                        )( _ )
                    ),
                TransformPercentages = 
                    List.Transform (
                        SplitNamesAndPercentage,
                        each Table.FromRows (
                            {
                                { _{0} }
                                    & List.Transform (
                                        List.Select (
                                            Text.Split ( _{1}, "%" ),
                                            each not List.Contains ( { " ", "" }, _ )
                                        ),
                                        each try Number.From ( _ ) / 100 otherwise null
                                    )
                            },
                            type table [
                                Name = text,
                                Percentage 1 = Percentage.Type,
                                Percentage 2 = Percentage.Type,
                                Percentage 3 = Percentage.Type,
                                Percentage 4 = Percentage.Type
                            ]
                        )
                    ),
                Result = Table.Combine ( TransformPercentages )
            in
                Result,
        type table [
            Name = text,
            Percentage 1 = Percentage.Type,
            Percentage 2 = Percentage.Type,
            Percentage 3 = Percentage.Type,
            Percentage 4 = Percentage.Type
        ]
    ),
    RemovedColumns = Table.RemoveColumns ( AddedCustom, { "Column1" } ),
    ExpandedSplit = 
        Table.ExpandTableColumn (
            RemovedColumns,
            "Split",
            { "Name", "Percentage 1", "Percentage 2", "Percentage 3", "Percentage 4" },
            { "Name", "Percentage 1", "Percentage 2", "Percentage 3", "Percentage 4" }
        )
in
    ExpandedSplit

     

2 Replies

  • AntrikshSharma's avatar
    AntrikshSharma
    Community Champion
    3 years ago

    Anonymous Use this:

    let
    Source = Table.FromRows (
        Json.Document (
            Binary.Decompress (
                Binary.FromText (
                    "jZLdjoIwEEZfhZhw2aYgFPosxotaBm0iP2mRjW+/04Laimv2xknMmW/OTDkcdrdT25LRNELQqkhSUVPBkjRjjDKs7t86SRUoaSZrwcxagcPrjJYiSfOMsixJF7iuacEce7PT0IF54zlb+DLkMxcecG4+5ooKS2ixrxyIoeoi+3cTzn3ynoXJJfKDabYaC5yLEC5c+ijvHcThPMeV1vAVr7gfhLjRSvfnf6gUnh6am/rLPItx201EDZ1bF6A/6x6IVFdy1TPUOS3RNi8pe20gsKxNfau9EhhiZ/VoYXi+fE9ZHcypsLieRtrLaZCmIS00vsEhKxf+ehjkFU960aON8GTDe/wsJ/iR90iFLyr8g8ryXkSO+p0uXjAXEfzdYkFH3YUHZHxzjEcoPtOsrR56fNmn9ZcFLUyT+wgi5w/HfuQ/+a12NGB3PP4C",
                    BinaryEncoding.Base64
                ),
                Compression.Deflate
            )
        ),
        let
            _t = ( ( type nullable text ) meta [ Serialized.Text = true ] )
        in
            type table [ Column1 = _t ]
    ),
    ChangedType = Table.TransformColumnTypes ( Source, { { "Column1", type text } } ),
    AddedCustom = 
    Table.AddColumn (
        ChangedType,
        "Split",
        ( Data ) =>
            let
                SplitToRows = 
                    Splitter.SplitTextByCharacterTransition (
                        { "%" },
                        { "a" .. "z", "A" .. "Z" }
                    )( Data[Column1] ),
                SplitNamesAndPercentage = 
                    List.Transform (
                        SplitToRows,
                        each Splitter.SplitTextByCharacterTransition (
                            { "a" .. "z", "A" .. "Z" },
                            { "0" .. "9" }
                        )( _ )
                    ),
                TransformPercentages = 
                    List.Transform (
                        SplitNamesAndPercentage,
                        each Table.FromRows (
                            {
                                { _{0} }
                                    & List.Transform (
                                        List.Select (
                                            Text.Split ( _{1}, "%" ),
                                            each not List.Contains ( { " ", "" }, _ )
                                        ),
                                        each try Number.From ( _ ) / 100 otherwise null
                                    )
                            },
                            type table [
                                Name = text,
                                Percentage 1 = Percentage.Type,
                                Percentage 2 = Percentage.Type,
                                Percentage 3 = Percentage.Type,
                                Percentage 4 = Percentage.Type
                            ]
                        )
                    ),
                Result = Table.Combine ( TransformPercentages )
            in
                Result,
        type table [
            Name = text,
            Percentage 1 = Percentage.Type,
            Percentage 2 = Percentage.Type,
            Percentage 3 = Percentage.Type,
            Percentage 4 = Percentage.Type
        ]
    ),
    RemovedColumns = Table.RemoveColumns ( AddedCustom, { "Column1" } ),
    ExpandedSplit = 
        Table.ExpandTableColumn (
            RemovedColumns,
            "Split",
            { "Name", "Percentage 1", "Percentage 2", "Percentage 3", "Percentage 4" },
            { "Name", "Percentage 1", "Percentage 2", "Percentage 3", "Percentage 4" }
        )
in
    ExpandedSplit

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      Wow.. that is very impressive! Thank you so much!