Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Imported data from multiple SharePoint lists not coming through under the same column

Hi 

 

I currently have a report which pulls data from multiple SharePoint (SP) lists with the same columns/structure but they are in different SP sites. 

 

These lists are maitained by a template in a SharePoint Content Type gallery, so technically each of these lists should carry the same characteristics in the list columns. 

 

However, the issue I am having is, when I 'Expand Items' from the combined table

1) there are some fields in columns that's coming under a separate column under a different name (slightly different)

2) Some not coming through at all, although there's data in the SP list column

 

Example below 

I've gone all the way back to the list settings, Parent list and the Content Type gallery to try and fix this. Nothing's worked so far. 

 

Has anyone else has this issue before? If so what did you have to do to fix this? 

 

Thank you 

Need Help
  • KNP's avatar
    KNP
    4 years ago

    I agree with what Ehren has said. The thing to watch out for with SharePoint lists, what the columns are visually named as are not neccessarily what they are called in the background.

     

    If you know the number of columns, you could define the new column names and rename all of the columns dynamically.

     

    Example, paste these into two separate queries to see what I mean. Alternately, attached PBIX file for your convenience.

    // Columns          
let
  Source = Table.FromRows(
    Json.Document(
      Binary.Decompress(
        Binary.FromText("i45Wcs4vzSspqlSK1YlWCijKTylNLgGzgxNzUouVYmMB", BinaryEncoding.Base64), 
        Compression.Deflate
      )
    ), 
    let
      _t = ((type nullable text) meta [Serialized.Text = true])
    in
      type table [NewColumns = _t]
  ), 
  #"Changed Type" = Table.TransformColumnTypes(Source, {{"NewColumns", type text}})
in
  #"Changed Type"

     

    // Table        
let
  Source = Table.FromRows(
    Json.Document(
      Binary.Decompress(
        Binary.FromText(
          "i45Wck7MS0xJVNIBMoqKUktSi0BsYyNjcwOlWJ1oJbeixLzkVAxpM6g0XLdvfl4JkA2SNDAyNMMpaWxsaGGuZwqW902tyEzOR5E3sjA3MQAaHQsA",
          BinaryEncoding.Base64
        ),
        Compression.Deflate
      )
    ),
    let
      _t = ((type nullable text) meta [Serialized.Text = true])
    in
      type table [C = _t, P = _t, S = _t]
  ),
  #"Changed Type" = Table.TransformColumnTypes(
    Source,
    {{"C", type text}, {"P", type text}, {"S", type number}}
  ),
  OldColumns = List.Buffer(Table.ColumnNames(#"Changed Type")),
  NewColumns = List.Buffer(Table.ToList(Columns)),
  RenameList = List.Buffer(List.Zip({OldColumns, NewColumns})),
  Rename = Table.RenameColumns(#"Changed Type", RenameList, MissingField.Ignore)
in
  Rename

     

3 Replies

  • KNP's avatar
    KNP
    Super User
    4 years ago

    I agree with what Ehren has said. The thing to watch out for with SharePoint lists, what the columns are visually named as are not neccessarily what they are called in the background.

     

    If you know the number of columns, you could define the new column names and rename all of the columns dynamically.

     

    Example, paste these into two separate queries to see what I mean. Alternately, attached PBIX file for your convenience.

    // Columns          
let
  Source = Table.FromRows(
    Json.Document(
      Binary.Decompress(
        Binary.FromText("i45Wcs4vzSspqlSK1YlWCijKTylNLgGzgxNzUouVYmMB", BinaryEncoding.Base64), 
        Compression.Deflate
      )
    ), 
    let
      _t = ((type nullable text) meta [Serialized.Text = true])
    in
      type table [NewColumns = _t]
  ), 
  #"Changed Type" = Table.TransformColumnTypes(Source, {{"NewColumns", type text}})
in
  #"Changed Type"

     

    // Table        
let
  Source = Table.FromRows(
    Json.Document(
      Binary.Decompress(
        Binary.FromText(
          "i45Wck7MS0xJVNIBMoqKUktSi0BsYyNjcwOlWJ1oJbeixLzkVAxpM6g0XLdvfl4JkA2SNDAyNMMpaWxsaGGuZwqW902tyEzOR5E3sjA3MQAaHQsA",
          BinaryEncoding.Base64
        ),
        Compression.Deflate
      )
    ),
    let
      _t = ((type nullable text) meta [Serialized.Text = true])
    in
      type table [C = _t, P = _t, S = _t]
  ),
  #"Changed Type" = Table.TransformColumnTypes(
    Source,
    {{"C", type text}, {"P", type text}, {"S", type number}}
  ),
  OldColumns = List.Buffer(Table.ColumnNames(#"Changed Type")),
  NewColumns = List.Buffer(Table.ToList(Columns)),
  RenameList = List.Buffer(List.Zip({OldColumns, NewColumns})),
  Rename = Table.RenameColumns(#"Changed Type", RenameList, MissingField.Ignore)
in
  Rename

     

  • Ehren's avatar
    Ehren
    Microsoft Employee
    4 years ago

    Issue #2 is likely caused by issue #1. You probably need to either fix the naming inconsistencies at the source (SharePoint), or add a set of conditional rename operations (for example, if the table has column "ListID#", then rename it to "List ID#").

     

    EDIT: Also, how are you combining the SP lists (merge/join or combine/append)?

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi 

    Thanks for all your replies. The issue was the Content reference point of eash Sharepoint list. They were being created and referenced to a parent list for consistency, but for some reason the list columns weren't picking up the same headings. Had slight variations as shown in the example. 

    Re-did all the lists again by referencing to one list and then breaking the permissions between them. This seemed to do the trick for us. It was definitely an issue with SharePoint as opposed to PBI.