[Table] as data value

[Anonymous]

I'm using a web service connection to import data from Version One - a project management task tracking tool. I'm importing data on workitems and the owners of each workitem. In the cases where there are multiple owners on a workitem, instead of listing out each owner with a semicolon or comma in between each name they are combined as a value listed as [Table]. In 'Edit Queries', I can double click on [Table] and it will show me a small table where each of the owners are listed out, but it doesn't assign each owner to the specific workitem.

Does anyone know how to break out the table to list out each owner name so I won't have [Table] in my data?

[v-juanli-msft]

Hi @Anonymous 

Open Advanced editor, write this code in

let

    Source = Excel.Workbook(File.Contents("C:\Users\maggiel\Desktop\case\4\4.27\4.27.xlsx"), null, true),

    Sheet7_Sheet = Source{[Item="Sheet7",Kind="Sheet"]}[Data],

    #"Promoted Headers" = Table.PromoteHeaders(Sheet7_Sheet, [PromoteAllScalars=true]),

    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"workitem", Int64.Type}}),

    #"Added Custom" = Table.AddColumn(#"Changed Type1", "Custom", each Table.FromValue({"mm",Table.FromValue({"a","b"}), "yy"})),

    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Value"}, {"Custom.Value"}),

    #"Added Index" = Table.AddIndexColumn(#"Expanded Custom", "Index", 0, 1),

    Source1 = Table.SelectRows(#"Added Index", each ([Index] = 1)),

    #"Expanded Custom.Value1" = Table.ExpandTableColumn(Source1, "Custom.Value", {"Value"}, {"Custom.Value.Value"}),

    #"Renamed Columns1" = Table.RenameColumns(#"Expanded Custom.Value1",{{"Custom.Value.Value", "Custom.Value"}}),

    #"Appended Query1" = Table.Combine({#"Renamed Columns1",#"Added Index" }),

    Source2 = Table.SelectRows(#"Added Index", each ([Index] = 4)),

    #"Expanded Custom.Value2" = Table.ExpandTableColumn(Source2, "Custom.Value", {"Value"}, {"Custom.Value.Value"}),

    #"Renamed Columns2" = Table.RenameColumns(#"Expanded Custom.Value2",{{"Custom.Value.Value", "Custom.Value"}}),

    #"Appended Query2" = Table.Combine({#"Renamed Columns2", #"Appended Query1"}),

    Source3 = Table.SelectRows(#"Added Index", each ([Index] = 7)),

    #"Expanded Custom.Value3" = Table.ExpandTableColumn(Source3, "Custom.Value", {"Value"}, {"Custom.Value.Value"}),

    #"Renamed Columns3" = Table.RenameColumns(#"Expanded Custom.Value3",{{"Custom.Value.Value", "Custom.Value"}}),

    #"Appended Query3" = Table.Combine({#"Renamed Columns3", #"Appended Query2"}),

    #"Changed Type2" = Table.TransformColumnTypes(#"Appended Query3",{{"Custom.Value", type text}}),

    #"Replaced Errors" = Table.ReplaceErrorValues(#"Changed Type2", {{"Custom.Value", null}}),

    #"Filtered Rows" = Table.SelectRows(#"Replaced Errors", each [Custom.Value] <> null and [Custom.Value] <> "")

in

#"Filtered Rows"

Best Regards
Maggie

[v-juanli-msft]

Hi @Anonymous 

I can reproduce your problem by clicking on "Table".

To assign each owner to the specific workitem, expand the "Value" column

To make the owners with a semicolon or comma for a workitem,

you could apply&&close, go to report view, create a measure with DAX,

Finally add them in a table visual as below

Measure = CONCATENATEX(ALLEXCEPT(Sheet7,Sheet7[workitem]),[Custom.Value],",")

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Thank you for the response @v-juanli-msft ,

Unfortunately that fix did not work. After expanding the value column, there are still [Table] values in the data. I'm not sure how to add a screenshot here to show you though

[v-juanli-msft]

Hi @Anonymous 

I will explain for the code step by step, once you understand, you could modify code in your scenario.

let

    Source = Excel.Workbook(File.Contents("C:\Users\maggiel\Desktop\case\4\4.27\4.27.xlsx"), null, true),                                                                                                                //get data from your data source

    Sheet7_Sheet = Source{[Item="Sheet7",Kind="Sheet"]}[Data],

    #"Promoted Headers" = Table.PromoteHeaders(Sheet7_Sheet, [PromoteAllScalars=true]),

    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"workitem", Int64.Type}}),

                           //change data type, this step perform automatically when importing data.
                          //This step is named as #"Changed Type" by default, You need to change     
                       //the step name to #"Changed Type1" to differ it with following Changed Type step

    #"Added Custom" = Table.AddColumn(#"Changed Type1", "Custom", each Table.FromValue({"mm",Table.FromValue({"a","b"}), "yy"})),

            //Add a custom column, I use this to reproduce your scenario, you don’t need to do this like me

    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Value"}, {"Custom.Value"}),

          //expand the column, this step does the same thing as my first post

    #"Added Index" = Table.AddIndexColumn(#"Expanded Custom", "Index", 0, 1),

            //Add an index column from 0

           // From here, we create three sources to extract the nested “Table” value from an expanded column

    Source1 = Table.SelectRows(#"Added Index", each ([Index] = 1)),

    #"Expanded Custom.Value1" = Table.ExpandTableColumn(Source1, "Custom.Value", {"Value"}, {"Custom.Value.Value"}),

    #"Renamed Columns1" = Table.RenameColumns(#"Expanded Custom.Value1",{{"Custom.Value.Value", "Custom.Value"}}),

    #"Appended Query1" = Table.Combine({#"Renamed Columns1",#"Added Index" }),

    Source2 = Table.SelectRows(#"Added Index", each ([Index] = 4)),

    #"Expanded Custom.Value2" = Table.ExpandTableColumn(Source2, "Custom.Value", {"Value"}, {"Custom.Value.Value"}),

    #"Renamed Columns2" = Table.RenameColumns(#"Expanded Custom.Value2",{{"Custom.Value.Value", "Custom.Value"}}),

    #"Appended Query2" = Table.Combine({#"Renamed Columns2", #"Appended Query1"}),

    Source3 = Table.SelectRows(#"Added Index", each ([Index] = 7)),

    #"Expanded Custom.Value3" = Table.ExpandTableColumn(Source3, "Custom.Value", {"Value"}, {"Custom.Value.Value"}),

    #"Renamed Columns3" = Table.RenameColumns(#"Expanded Custom.Value3",{{"Custom.Value.Value", "Custom.Value"}}),

    #"Appended Query3" = Table.Combine({#"Renamed Columns3", #"Appended Query2"}),

    //below we will change the data type of the column to text, thus, if the row shows “Table”, it will turn to error.

    #"Changed Type2" = Table.TransformColumnTypes(#"Appended Query3",{{"Custom.Value", type text}}),

    #"Replaced Errors" = Table.ReplaceErrorValues(#"Changed Type2", {{"Custom.Value", null}}),

    #"Filtered Rows" = Table.SelectRows(#"Replaced Errors", each [Custom.Value] <> null and [Custom.Value] <> "")

in

    #"Filtered Rows"

[Anonymous]

Hi @v-juanli-msft ,

Can I set up a webex call with you to discuss and walk through your code? I am having a hard time following your screenshots and I would like to fully understand what I am doing in case I have a similar issue in the future.

Thanks,

Jordan

[v-juanli-msft]

Hi @Anonymous 

You can upload picture or file in One Drive, then share the link here.

Best Regards
Maggie

[Anonymous]

Hi @v-juanli-msft ,

Here is a screenshot showing my issue. There are still [Table] values showing after I expand the attribute value. Please let me know if there is a work around to expand all of the tables. I'm worried that Power BI may have options to keep expanding out one level at a time instead of expanding out all of the tables into individual rows regardless of how many owners are associated with a workitem.

[v-juanli-msft]

Hi @Anonymous 

I can reproduce your problem:

The table value is "a", "b" for each row in my example.

Then i modify the code in Advanced editor and finally get the results as below:

I will post another to illustrate my solution clearly.

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.