Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to Solution.
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 ;). |
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. | Proud to be a Super User! |
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.
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 ;). |
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. | Proud to be a Super User! |
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)?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.