Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Anonymous
Not applicable

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 

msfer_0-1634019043523.png

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 

1 ACCEPTED SOLUTION
KNP
Super User
Super User

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

 

Have I solved your problem?
Please click Accept as Solution so I don't keep coming back to this post, oh yeah, others may find it useful also ;).
chrome-9xf-Zagzel-B

If you found this post helpful, please give Kudos.
It gives me a sense of instant gratification and, if you give me Kudos enough times, magical unicorns will appear on your screen.
If you find my signature vaguely amusing, please give Kudos.
KIfp67uy-Sr
Proud to be a Super User!PBI-Super-User-Rank-30x30-1x

View solution in original post

3 REPLIES 3
Anonymous
Not applicable

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. 

KNP
Super User
Super User

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

 

Have I solved your problem?
Please click Accept as Solution so I don't keep coming back to this post, oh yeah, others may find it useful also ;).
chrome-9xf-Zagzel-B

If you found this post helpful, please give Kudos.
It gives me a sense of instant gratification and, if you give me Kudos enough times, magical unicorns will appear on your screen.
If you find my signature vaguely amusing, please give Kudos.
KIfp67uy-Sr
Proud to be a Super User!PBI-Super-User-Rank-30x30-1x
Ehren
Microsoft Employee
Microsoft Employee

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

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors
Top Kudoed Authors